package com.hsnn.medstgmini.pro.base.dao.Impl;

import java.math.BigInteger;
import java.sql.Timestamp;
import java.util.ArrayList;
import java.util.Arrays;
import java.util.Date;
import java.util.HashMap;
import java.util.Iterator;
import java.util.List;
import java.util.Map;
import java.util.Properties;
import java.util.UUID;

import javax.annotation.Resource;

import org.apache.commons.lang.StringUtils;
import org.hibernate.Query;
import org.hibernate.SQLQuery;
import org.hibernate.Session;
import org.hibernate.SessionFactory;
import org.hibernate.transform.Transformers;
import org.springframework.beans.BeanUtils;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.beans.factory.annotation.Value;
import org.springframework.stereotype.Repository;

import com.hsnn.medstgmini.Constants;
import com.hsnn.medstgmini.base.dict.dao.DicAreaDao;
import com.hsnn.medstgmini.base.dict.model.DicSys;
import com.hsnn.medstgmini.base.std.dao.StdCompinfoDao;
import com.hsnn.medstgmini.base.std.dao.StdProdDrugInfoDao;
import com.hsnn.medstgmini.base.std.model.StdCompinfo;
import com.hsnn.medstgmini.base.std.model.StdDrugQualitySpec;
import com.hsnn.medstgmini.base.std.model.form.StdProdDrugInfoForm;
import com.hsnn.medstgmini.dao.HibernateSqlUtil;
import com.hsnn.medstgmini.exception.MSException;
import com.hsnn.medstgmini.model.DrugStatus;
import com.hsnn.medstgmini.model.Status;
import com.hsnn.medstgmini.pro.base.dao.BaseGoodsDao;
import com.hsnn.medstgmini.pro.base.model.BaseDelrelation;
import com.hsnn.medstgmini.pro.base.model.BaseDelrelationLog;
import com.hsnn.medstgmini.pro.base.model.BaseGoods;
import com.hsnn.medstgmini.pro.base.model.FilesSaveInfo;
import com.hsnn.medstgmini.pro.base.model.GoodsSource;
import com.hsnn.medstgmini.pro.base.model.form.BaseDelrelationForm;
import com.hsnn.medstgmini.pro.project.dao.BidProjectDao;
import com.hsnn.medstgmini.pro.project.model.BidProject;
import com.hsnn.medstgmini.pro.project.model.BidProjectDetails;
import com.hsnn.medstgmini.service.CommonService;
import com.hsnn.medstgmini.sys.model.SysImportFileInfo;
import com.hsnn.medstgmini.sys.model.SysUser;
import com.hsnn.medstgmini.sys.service.SysImportFileInfoManager;
import com.hsnn.medstgmini.trading.catalog.dao.PurHospCatalogDao;
import com.hsnn.medstgmini.trading.catalog.model.form.GoodsSelectForm;
import com.hsnn.medstgmini.trading.pur.model.form.DrugNoJNoX;
import com.hsnn.medstgmini.trading.pur.service.PurConfigManager;
import com.hsnn.medstgmini.util.AreaUtil;
import com.hsnn.medstgmini.util.DateUtil;
import com.hsnn.medstgmini.util.DicSysUtil;
import com.hsnn.medstgmini.util.Pagination;
import com.hsnn.medstgmini.util.ParseDate;
import com.hsnn.medstgmini.util.ParseNumber;
import com.hsnn.medstgmini.util.PinYin;

@Repository("baseGoodsDao")
public class BaseGoodsDaoImpl implements BaseGoodsDao {
	@Resource(name = "sessionFactory")
	private SessionFactory sessionFactory;
	@Autowired
	private HibernateSqlUtil dao;
	@Autowired
	private SysImportFileInfoManager sfmanager;
	@Autowired
	private CommonService commonService;
	@Autowired
	private DicAreaDao areaDao;
	@Autowired
	private StdCompinfoDao compDao;
	@Autowired
	private PurConfigManager purConfigManager;
	@Autowired
	private StdProdDrugInfoDao stdDrugInfoDao; 
	@Autowired
	private StdCompinfoDao stdCompinfoDao;
	@Autowired
	private PurHospCatalogDao purHospCatalogDao; 
	@Autowired
	private BidProjectDao bidProjectDao;
	@Value("#{projareaProp['projarea.enable']}")
    private String enable;
	@Value("#{projareaProp['projarea.GZJYCG_1']}")
    private String GZJYCG_1;
	@Value("#{projareaProp['projarea.GZJYCG_2']}")
    private String GZJYCG_2;
	@Autowired
	private Properties projareaProp;
	private Session getSession() {
		Session session = sessionFactory.getCurrentSession();
		if (null == session) {
			session = sessionFactory.openSession();
		}
		return session;
	}
	/**
	 * 
	 * @category 跳转到商品列表页面
	 * @author 邱磊
	 * @date 2015年6月17日 下午8:52:48
	 * @param page
	 * @return
	 */
	public Pagination getDataList(Pagination page) {
		StringBuffer sql = new StringBuffer();
		sql.append(" SELECT a.drug_medicine drugMedicine,a.status, a.goods_code goodsCode, a.area_id areaId, a.area_name areaName, "); 
		sql.append(" a.ar_shortname arShortname, a.ar_fullname arFullname, a.proj_id projId, ");
		sql.append(" a.proj_name projName, a.proj_starttime projStarttime, a.proj_endtime projEndtime, ");
		sql.append(" a.proj_status projStatus, a.drug_code drugCode, a.cat_comp_code catCompCode, ");
		sql.append(" a.cat_comp_name catCompName, a.use_range useRange, a.drug_attr drugAttr, ");
		sql.append(" a.goods_source goodsSource, a.pattern_type patternType, a.is_healthcare isHealthcare, ");
		sql.append(" a.is_agriculture isAgriculture, a.is_lowprice isLowprice, a.is_smallprice isSmallprice, ");
		sql.append(" a.is_smallsource isSmallsource, a.is_longtime_plan isLongtimePlan, a.drug_cat drugCat, ");
		sql.append(" a.pro_price_limit proPriceLimit, a.retail_price_limit retailPriceLimit, ");
		sql.append(" a.hang_price hangPrice, a.ref_price refPrice, a.healthcare_price healthcarePrice, ");
		sql.append(" a.qualityspec_name qualityspecName, a.form_name formName, a.spec_name specName, ");
		sql.append(" a.material_name materialName, a.unit_name unitName, a.factor, a.prod_compcode compCode, ");
		sql.append(" a.prod_compname compName,b.dic_name goodsSourceName FROM base_goods_item a, (select dic_code, dic_name from dic_sysdic c where c.classify_code=114) b where a.goods_source = b.dic_code and a.proj_status = 1 ");
		sql.append(" AND a.proj_starttime <= SYSDATE() AND a.proj_endtime >= SYSDATE() ");
		sql.append(" AND a.prod_compstatus = 1 AND a.qualityspec_status = 1 AND a.drug_status = 1 ");
		
		BaseGoods form = (BaseGoods) page.getForm();
		Map<String, Object> params = new HashMap<String, Object>();
		if (null != form) {
			if (StringUtils.isNotBlank(form.getDrugName())) {
				sql.append(" and (a.qualityspec_name LIKE :drugName or a.qualityspec_name_spel LIKE :drugName) ");
				params.put("drugName", "%" + form.getDrugName().toUpperCase() + "%");
			}
			if (StringUtils.isNotBlank(form.getProjName())) {
				sql.append(" and a.proj_name LIKE :projName ");
				params.put("projName", "%" + form.getProjName() + "%");
			}
			if (StringUtils.isNotBlank(form.getCompName())) {
				sql.append(" and (a.prod_compname LIKE :compName or a.prod_compname_spel LIKE :compName) ");
				params.put("compName", "%" + form.getCompName() + "%");
			}
			if (StringUtils.isNotBlank(form.getGoodsCode())) {
				sql.append(" and a.goods_code = :goodsCode ");
				params.put("goodsCode", form.getGoodsCode());
			}
			if (StringUtils.isNotBlank(form.getDrugCode())) {
				sql.append(" and a.drug_code = :drugCode ");
				params.put("drugCode", form.getDrugCode());
			}
			if (StringUtils.isNotBlank(form.getCatCompName())) {
				sql.append(" and (a.cat_comp_name LIKE :catCompName or a.cat_comp_name_spel LIKE :catCompName) ");
				params.put("catCompName", "%" + form.getCatCompName() + "%");
			}
			if (form.getIsLowprice() != null) {
				sql.append(" and a.is_lowprice = :isLowprice ");
				params.put("isLowprice", form.getIsLowprice());
			}
			if (form.getIsSmallprice() != null) {
				sql.append(" and a.is_smallprice = :isSmallprice ");
				params.put("isSmallprice", form.getIsSmallprice());
			}
			if (form.getIsSmallsource() != null) {
				sql.append(" and a.is_smallsource = :isSmallsource ");
				params.put("isSmallsource", form.getIsSmallsource());
			}
			if (form.getGoodsSource() != null) {
				sql.append(" and a.goods_source = :goodsSource ");
				params.put("goodsSource", form.getGoodsSource());
			}
			if (form.getStatus() != null) {
				sql.append(" and a.status = :status ");
				params.put("status", form.getStatus());
			}
		}
		page = dao.getPageMapList(sql.toString(), params, page);
		return page;
	}
	
	/**
	 * @category 卫生局查看商品目录
	 * @date 2015年11月16日
	 * @param page
	 * @return
	 */
	public Pagination getGoodsListByWSJ(Pagination page, SysUser loginUser) {
		StringBuffer sql = new StringBuffer();
		sql.append(" SELECT a.area_id FROM std_healthbureau a WHERE a.hea_bur_id = :orgId ");
		Session session = getSession();
		SQLQuery query = session.createSQLQuery(sql.toString());
		query.setString("orgId", loginUser.getOrgId());
		String areaId = (String) query.uniqueResult();
		
		if (StringUtils.isBlank(areaId)) {
			return page;
		}
		
		sql = new StringBuffer();
		sql.append(" SELECT a.`status`, a.goods_code goodsCode, a.area_id areaId, a.area_name areaName, "); 
		sql.append(" a.ar_shortname arShortname, a.ar_fullname arFullname, a.proj_id projId, ");
		sql.append(" a.proj_name projName, a.proj_starttime projStarttime, a.proj_endtime projEndtime, ");
		sql.append(" a.proj_status projStatus, a.drug_code drugCode, a.cat_comp_code catCompCode, ");
		sql.append(" a.cat_comp_name catCompName, a.use_range useRange, a.drug_attr drugAttr, ");
		sql.append(" a.goods_source goodsSource, a.pattern_type patternType, a.is_healthcare isHealthcare, ");
		sql.append(" a.is_agriculture isAgriculture, a.is_lowprice isLowprice, a.is_smallprice isSmallprice, ");
		sql.append(" a.is_smallsource isSmallsource, a.is_longtime_plan isLongtimePlan, a.drug_cat drugCat, ");
		sql.append(" a.pro_price_limit proPriceLimit, a.retail_price_limit retailPriceLimit, ");
		sql.append(" a.hang_price hangPrice, a.ref_price refPrice, a.healthcare_price healthcarePrice, ");
		sql.append(" a.qualityspec_name qualityspecName, a.form_name formName, a.spec_name specName, ");
		sql.append(" a.material_name materialName, a.unit_name unitName, a.factor, a.prod_compcode compCode, ");
		sql.append(" a.prod_compname compName FROM base_goods_item a WHERE a.proj_status = 1 ");
		sql.append(" AND a.proj_starttime <= SYSDATE() AND a.proj_endtime >= SYSDATE() and a.status = 1 ");
		sql.append(" AND a.prod_compstatus = 1 AND a.qualityspec_status = 1 AND a.drug_status = 1 ");
		sql.append(" AND ").append(AreaUtil.getDrugByArea(areaId, "a.area_id"));
		
		BaseGoods form = (BaseGoods) page.getForm();
		Map<String, Object> params = new HashMap<String, Object>();
		if (null != form) {
			if (StringUtils.isNotBlank(form.getDrugName())) {
				sql.append(" and (a.qualityspec_name LIKE :drugName or a.qualityspec_name_spel LIKE :drugName) ");
				params.put("drugName", "%" + form.getDrugName() + "%");
			}
			if (StringUtils.isNotBlank(form.getProjName())) {
				sql.append(" and a.proj_name LIKE :projName ");
				params.put("projName", "%" + form.getProjName() + "%");
			}
			if (StringUtils.isNotBlank(form.getCompName())) {
				sql.append(" and (a.prod_compname LIKE :compName or a.prod_compname_spel LIKE :compName) ");
				params.put("compName", "%" + form.getCompName() + "%");
			}
			if (StringUtils.isNotBlank(form.getGoodsCode())) {
				sql.append(" and a.goods_code = :goodsCode ");
				params.put("goodsCode", form.getGoodsCode());
			}
			if (StringUtils.isNotBlank(form.getDrugCode())) {
				sql.append(" and a.drug_code = :drugCode ");
				params.put("drugCode", form.getDrugCode());
			}
			if (StringUtils.isNotBlank(form.getCatCompName())) {
				sql.append(" and (a.cat_comp_name LIKE :catCompName or a.cat_comp_name_spel LIKE :catCompName) ");
				params.put("catCompName", "%" + form.getCatCompName() + "%");
			}
			if (form.getIsLowprice() != null) {
				sql.append(" and a.is_lowprice = :isLowprice ");
				params.put("isLowprice", form.getIsLowprice());
			}
			if (form.getIsSmallprice() != null) {
				sql.append(" and a.is_smallprice = :isSmallprice ");
				params.put("isSmallprice", form.getIsSmallprice());
			}
			if (form.getIsSmallsource() != null) {
				sql.append(" and a.is_smallsource = :isSmallsource ");
				params.put("isSmallsource", form.getIsSmallsource());
			}
			if (form.getGoodsSource() != null) {
				sql.append(" and a.goods_source = :goodsSource ");
				params.put("goodsSource", form.getGoodsSource());
			}
		}
		page = dao.getPageMapList(sql.toString(), params, page);
		return page;
	}

	/**
	 * 
	 * @category 启用停用
	 * @author 邱磊
	 * @date 2015年6月17日 下午8:53:29
	 * @param goodsCode
	 *            商品码
	 * @param status
	 *            1：启用，0：停用
	 * @return
	 */
	public void switchStatus(String goodsCode, Short status, String updUser) {
		BaseGoods data = getBaseGoodsById(goodsCode);
		if (0 == status){
			Map<String, Object> params = new HashMap<String, Object>();
			String sql = "DELETE from pur_purplan where goods_code=:goodsCode";
			params.put("goodsCode", data.getGoodsCode());
			dao.executeSql(sql, params);
			sql = "DELETE from pur_hospcatalog where goods_code=:goodsCode";
			dao.executeSql(sql, params);
		}
		String sql = "update base_goods_item set status = :status, upd_user = :updUser, upd_datetime = sysdate() where goods_code = :goodsCode";
		Map<String, Object> params = new HashMap<String, Object>();
		params.put("status", status);
		params.put("updUser", updUser);
		params.put("goodsCode", goodsCode);
		dao.executeSql(sql, params);
		writeLog(data);
	}

	/**
	 * 
	 * @category 批量启用停用
	 * @author 邱磊
	 * @date 2015年6月17日 下午8:54:03
	 * @param list
	 *            需要启用、停用的数据
	 * @return
	 */
	public void batchSwitchStatus(List<BaseGoods> list) {
		if (null != list && list.size() > 0) {
			for (int i = 0; i < list.size(); i++) {
				BaseGoods data = list.get(i);
				switchStatus(data.getGoodsCode(), data.getStatus(), data.getUpdUser());
			}
		}
	}
	public void uploadFile(FilesSaveInfo info){
		dao.add(info);
	}
	public FilesSaveInfo getFilesSaveInfoById(String id){
		return (FilesSaveInfo) dao.getUniquerResult(FilesSaveInfo.class, id);
	}
	public void updateFiles(FilesSaveInfo info){
		dao.update(info);
	}
	@SuppressWarnings("unchecked")
	public List<?> getProjectByName(String projectName){
		Session session = sessionFactory.openSession();
		String sql = "select * from bid_project t where t.name = :projNmae";
		Map<String, Object> params = new HashMap<String, Object>();
		params.put("projNmae", projectName);
		Query query = session.createSQLQuery(sql.toString()).setCacheable(true);
		if (params != null) {
			Iterator<?> list = params.entrySet().iterator();
			while (list.hasNext()) {
				Map.Entry<String, ?> entry = (Map.Entry<String, ?>) list.next();
				query.setParameter((String) entry.getKey(), entry.getValue());
			}
		}
		query.setResultTransformer(Transformers.ALIAS_TO_ENTITY_MAP);
		List<Map<String, Object>> list = query.list();
		session.clear();
		session.close();
		return list;
	}
	public BidProjectDetails getProjDetail(String projId){
		Session session = sessionFactory.openSession();
		BidProjectDetails data = (BidProjectDetails) session.get(BidProjectDetails.class, projId);
		session.clear();
		session.close();
		return data;
	}
	/**
	 * 
		 * 
		 *@category 根据药品编码获取产品信息
		 *@author 邱磊
		 *@date 2015年11月11日 上午11:00:36
		 *@param 
		 *@return List<?>
	 */
	@SuppressWarnings("unchecked")
	public List<?> getProDrugByDrugCode(String drugCode){
		Session session = sessionFactory.openSession();
		String sql = "SELECT * from std_proddruginfo a where a.drug_code=:drugCode";
		Map<String, Object> params = new HashMap<String, Object>();
		params.put("drugCode", drugCode);
		Query query = session.createSQLQuery(sql.toString()).setCacheable(true);
		if (params != null) {
			Iterator<?> list = params.entrySet().iterator();
			while (list.hasNext()) {
				Map.Entry<String, ?> entry = (Map.Entry<String, ?>) list.next();
				query.setParameter((String) entry.getKey(), entry.getValue());
			}
		}
		query.setResultTransformer(Transformers.ALIAS_TO_ENTITY_MAP);
		List<Map<String, Object>> list = query.list();
		session.clear();
		session.close();
		return list;
	}
	/**
	 * 
		 * 
		 *@category 根据项目ID与品规Id查询采购项目药品
		 *@author 邱磊
		 *@date 2015年11月11日 上午11:13:29
		 *@param 
		 *@return List<?>
	 */
	@SuppressWarnings("unchecked")
	public List<?> getPorjDrug(String projId,String qualityspecid){
		Session session = sessionFactory.openSession();
		String sql = "SELECT * from bid_projdrugs a where a.proj_id=:projId and a.drug_id=:drugId";
		Map<String, Object> params = new HashMap<String, Object>();
		params.put("projId", projId);
		params.put("drugId", qualityspecid);
		Query query = session.createSQLQuery(sql.toString()).setCacheable(true);
		if (params != null) {
			Iterator<?> list = params.entrySet().iterator();
			while (list.hasNext()) {
				Map.Entry<String, ?> entry = (Map.Entry<String, ?>) list.next();
				query.setParameter((String) entry.getKey(), entry.getValue());
			}
		}
		query.setResultTransformer(Transformers.ALIAS_TO_ENTITY_MAP);
		List<Map<String, Object>> list = query.list();
		session.clear();
		session.close();
		return list;
	}
	@SuppressWarnings("unchecked")
	public List<?> getHaveGoodsInfo(Map<String, String> map, BidProjectDetails detail){
		Session session = sessionFactory.openSession();
		StringBuffer sql = new StringBuffer()
		.append("select * from base_goods_item t ")
		.append(" where t.proj_id = :projId ");
		Map<String, Object> params = new HashMap<String, Object>();
		params.put("projId", map.get("projId"));
		if (StringUtils.isNotBlank( map.get("areaId"))){
			sql.append(" and t.area_id = :areaId");
			params.put("areaId", map.get("areaId"));
		}
		if (StringUtils.isNotBlank( map.get("drugCode"))){
			sql.append(" and t.drug_code = :drugCode");
			params.put("drugCode", map.get("drugCode"));
		}
		if (StringUtils.isNotBlank( map.get("catCompCode"))){
			sql.append(" and t.cat_comp_code = :catCompCode");
			params.put("catCompCode", map.get("catCompCode"));
		}
		if (StringUtils.isNotBlank( map.get("proPriceLimit"))){
			sql.append(" and t.pro_price_limit = :proPriceLimit");
			params.put("proPriceLimit", map.get("proPriceLimit"));
		}
		if (StringUtils.isNotBlank( map.get("qualityId"))){
			sql.append(" and t.qualityspec_id = :qualityspecId");
			params.put("qualityspecId", map.get("qualityId"));
		}
		Query query = session.createSQLQuery(sql.toString()).setCacheable(true);
		if (params != null) {
			Iterator<?> list = params.entrySet().iterator();
			while (list.hasNext()) {
				Map.Entry<String, ?> entry = (Map.Entry<String, ?>) list.next();
				query.setParameter((String) entry.getKey(), entry.getValue());
			}
		}
		query.setResultTransformer(Transformers.ALIAS_TO_ENTITY_MAP);
		List<Map<String, Object>> list = query.list();
		session.clear();
		session.close();
		return list;
	}
	
	@SuppressWarnings("unchecked")
	public List<?> getWaitGoodsInfo(Map<String, String> map, BidProjectDetails detail, String fileId){
		Session session = sessionFactory.openSession();
		StringBuffer sql = new StringBuffer()
		.append("select * from base_goods_import t ")
		.append(" where t.proj_id = :projId ");
		Map<String, Object> params = new HashMap<String, Object>();
		params.put("projId", map.get("projId"));
		if (StringUtils.isNotBlank( map.get("areaId"))){
			sql.append(" and t.area_id = :areaId");
			params.put("areaId", map.get("areaId"));
		}
		if (StringUtils.isNotBlank( map.get("drugCode"))){
			sql.append(" and t.drug_code = :drugCode");
			params.put("drugCode", map.get("drugCode"));
		}
		if (StringUtils.isNotBlank( map.get("catCompCode"))){
			sql.append(" and t.cat_comp_code = :catCompCode");
			params.put("catCompCode", map.get("catCompCode"));
		}
		if (StringUtils.isNotBlank( map.get("proPriceLimit"))){
			sql.append(" and t.pro_price_limit = :proPriceLimit");
			params.put("proPriceLimit", map.get("proPriceLimit"));
		}
		if (StringUtils.isNotBlank( map.get("qualityId"))){
			sql.append(" and t.qualityspec_id = :qualityspecId");
			params.put("qualityspecId", map.get("qualityId"));
		}
		sql.append(" and t.file_id = :fileId");
		params.put("fileId", fileId);
		Query query = session.createSQLQuery(sql.toString()).setCacheable(true);
		if (params != null) {
			Iterator<?> list = params.entrySet().iterator();
			while (list.hasNext()) {
				Map.Entry<String, ?> entry = (Map.Entry<String, ?>) list.next();
				query.setParameter((String) entry.getKey(), entry.getValue());
			}
		}
		query.setResultTransformer(Transformers.ALIAS_TO_ENTITY_MAP);
		List<Map<String, Object>> list = query.list();
		session.clear();
		session.close();
		return list;
	}
	
	public String getSysDicValue(String dicName){
		List<DicSys> dslist = DicSysUtil.getDicListByClassCode(Constants.QUALITYSPEC_CLASS);
		String val = "";
		for (int i = 0; i < dslist.size(); i++){
			DicSys data = dslist.get(i);
			if (dicName.equals(data.getDicName())){
				val = data.getDicCode();
				break;
			}
		}
		return val;
	}
	
	@SuppressWarnings("unchecked")
	public void addImportGoods(Map<String, Object> pmap, Map<String, Object> dmap, Map<String, Object> qmap, Map<String, Object> area, Map<String, Object> catcomp, Map<String, String> map, SysImportFileInfo info){
		Session session = sessionFactory.openSession();
		String sql = "select * from std_proddruginfo_detail a where a.prod_id=:prodId";
		Query query = session.createSQLQuery(sql.toString()).setCacheable(true);
		query.setParameter("prodId", dmap.get("prod_id").toString());
		query.setResultTransformer(Transformers.ALIAS_TO_ENTITY_MAP);
		List<Map<String, Object>> list = query.list();
		Map<String, Object> ddmap = null;
		if (null != list && list.size() > 0){
			ddmap = list.get(0);
		}
		StringBuffer asql = new StringBuffer()
		.append("insert into base_goods_import values (CONCAT('G',LPAD(:goodsCode,8,'0')),:areaId,:areaName,:arShortName,:arFullName,:projId,:projName,:projStarttime,:projEndtime,:projStatus,")
		.append(":qualityspecId,:drugCode,")
		.append(":qualityspecName,:qualityspecNameSpel,:qualityspecNameEng,:formName,:specName,:qualityspecStatus,")
		.append(":busName,:factor,:unitName,:materialName,:prodCompcode,:prodCompname,:prodCompnameSpel,:prodCompstatus,")
		.append(":catCompCode,:catCompName,:catCompNameSepl,:catCompStatus,:useRange,:drugAttr,:goodsSource,")
		.append(":patternType,:isHealthcar,:isAgriculture,:isLowprice,:isSmallprice,:isSmallsource,:isLongtimePlan,:drugStatus,:drugCat,")
		.append(":proPriceLimit,:retailPriceLimit,:hangPrice,:refPrice,:healthCarePrice,:picUrl,:picShortUrl,1,:fileId,:updUser,SYSDATE())");
		Map<String, Object> params = new HashMap<String, Object>();
		params.put("goodsCode", commonService.getSequence("goodsCode"));
		params.put("areaId", area==null?null:area.get("area_id"));
		params.put("areaName", area==null?null:area.get("area_name"));
		params.put("arShortName", area==null?null:area.get("ar_shortname"));
		params.put("arFullName", area==null?null:area.get("ar_fullname"));
		params.put("projId", pmap.get("proj_id"));
		params.put("projName", pmap.get("name"));
		params.put("projStarttime", pmap.get("starttime")==null?null:ParseDate.fromFullFormat(pmap.get("starttime").toString()));
		params.put("projEndtime", pmap.get("endtime")==null?null:ParseDate.fromFullFormat(pmap.get("endtime").toString()));
		params.put("projStatus", pmap.get("status"));
		params.put("qualityspecId", dmap.get("qualityspec_id"));
		params.put("drugCode", dmap.get("drug_code"));
		params.put("qualityspecName", qmap.get("qualityspec_name"));
		params.put("qualityspecNameSpel", qmap.get("qualityspec_name_spel"));
		params.put("qualityspecNameEng", qmap.get("qualityspec_name_eng"));
		params.put("formName", qmap.get("form_name"));
		params.put("specName", qmap.get("spec_name"));
		params.put("qualityspecStatus", qmap.get("status"));
		params.put("busName", dmap.get("bus_name"));
		params.put("factor", dmap.get("factor"));
		params.put("unitName", dmap.get("unit_name"));
		params.put("materialName", dmap.get("material_name"));
		params.put("prodCompcode", dmap.get("comp_code"));
		params.put("prodCompname", dmap.get("comp_name"));
		params.put("prodCompnameSpel", dmap.get("comp_name_spel"));
		params.put("prodCompstatus", dmap.get("comp_status"));
		params.put("catCompCode", catcomp.get("comp_code"));
		params.put("catCompName", catcomp.get("comp_name"));
		params.put("catCompNameSepl", catcomp.get("comp_spel_code"));
		params.put("catCompStatus", catcomp.get("status"));
		params.put("hangPrice", map.get("13"));
		params.put("refPrice", map.get("14"));
		params.put("healthCarePrice", map.get("15"));
		params.put("picUrl", map.get("16"));
		params.put("picShortUrl", map.get("17"));
		params.put("useRange", pmap.get("proj_range"));
		params.put("drugAttr", ddmap==null?2:ddmap.get("drug_attr")==null?2:"".equals(ddmap.get("drug_attr").toString())?2:1);
		params.put("goodsSource", ParseNumber.toInteger(pmap.get("proj_cat").toString()));
		params.put("patternType", ddmap==null?9:ddmap.get("pattern_type")==null?9:"".equals(ddmap.get("pattern_type").toString())?9:ParseNumber.toInteger(ddmap.get("pattern_type").toString()));
		params.put("isHealthcar", ddmap==null?9:ddmap.get("is_healthcare")==null?9:"".equals(ddmap.get("is_healthcare").toString())?9:ParseNumber.toInteger(ddmap.get("is_healthcare").toString()));
		params.put("isAgriculture", ddmap==null?9:ddmap.get("is_agriculture")==null?9:"".equals(ddmap.get("is_agriculture").toString())?9:ParseNumber.toInteger(ddmap.get("is_agriculture").toString()));
		params.put("isLowprice", ddmap==null?9:ddmap.get("is_lowprice")==null?9:"".equals(ddmap.get("is_lowprice").toString())?9:ParseNumber.toInteger(ddmap.get("is_lowprice").toString()));
		params.put("isSmallprice", 9);
		params.put("isSmallsource", 9);
		params.put("isLongtimePlan", 0);
		params.put("drugStatus", dmap.get("status"));
		params.put("drugCat", getSysDicValue(map.get("2").toString()));
		params.put("proPriceLimit", ParseNumber.toDouble(map.get("11")));
		params.put("retailPriceLimit", ParseNumber.toDouble(map.get("12")));
		params.put("updUser", info.getUserId());
		params.put("fileId", info.getId());
		Query aquery = session.createSQLQuery(asql.toString());
		if (params != null) {
			Iterator<?> pl = params.entrySet().iterator();
			while (pl.hasNext()) {
				Map.Entry<String, ?> entry = (Map.Entry<String, ?>) pl.next();
				aquery.setParameter((String) entry.getKey(), entry.getValue());
			}
		}
		aquery.executeUpdate();
		session.clear();
		session.close();
	}
	
	public void deleteImportGoods(String fileId){
		Session session = sessionFactory.openSession();
		String sql = "delete from base_goods_import where file_id=:fileId";
		Query aquery = session.createSQLQuery(sql.toString());
		aquery.setParameter("fileId", fileId);
		aquery.executeUpdate();
		session.clear();
		session.close();
	}
	/**
	 * 
		 * 
		 *@category 根据品规ID获取品规信息
		 *@author 邱磊
		 *@date 2015年11月11日 上午11:21:01
		 *@param 
		 *@return List<?>
	 */
	public StdDrugQualitySpec getQualitySpecById(String id){
		Session session = sessionFactory.openSession();
		StdDrugQualitySpec data = (StdDrugQualitySpec) session.get(StdDrugQualitySpec.class, id);
		session.clear();
		session.close();
		return data;
	}
	/**
	 * 
	 * @category 新增商品
	 * @author 邱磊
	 * @date 2015年6月17日 下午8:54:37
	 * @param data
	 * @return
	 */
	public void addData(BaseGoods data) {
		getSession().save(data);
		getSession().flush();
		writeLog(data);
	}

	/**
	 * 
	 * @category 批量导入
	 * @author 邱磊
	 * @date 2015年6月17日 下午8:55:31
	 * @param list
	 *            有效数据List
	 * @return
	 */
	public void bathImport(List<BaseGoods> list) {
		if (null != list && list.size() > 0) {
			for (int i = 0; i < list.size(); i++) {
				BaseGoods data = list.get(i);
				data.setStatus(Short.valueOf("1"));
				dao.add(data);
			}
		}
	}

	/**
	 * 
	 *@category 根据商品编码查询商品信息
	 *@author 邱磊
	 *@date 2015年6月17日 下午9:06:42
	 *@param goodsCode
	 *@return
	 */
	public BaseGoods getBaseGoodsById(String goodsCode) {
		BaseGoods data = (BaseGoods) getSession().get(BaseGoods.class, goodsCode);
		return data;
	}
	

	/**
	 * 
	 *@category 根据商品编码查询商品信息
	 *@author 邱磊
	 *@date 2015年6月17日 下午9:06:42
	 *@param goodsCode
	 *@return
	 */
	@SuppressWarnings("unchecked")
	@Override
	public Map<String, Object> getBaseGoodsByCode(String goodsCode) {
		StringBuffer sql = new  StringBuffer();
		sql.append("select * ");
		sql.append(" from base_goods_item a ");
		sql.append(" where a.goods_code=:goodsCode   ");
		Map<String, Object> params = new HashMap<String, Object>();
		params.put("goodsCode", goodsCode);
		List<Map<String, Object>> list = (List<Map<String, Object>>) dao.getSqlMapList(sql.toString(), params);
		if (null != list && list.size()  > 0){
			return list.get(0);
		}
		return null;
	}

	/**
	 * 
	 *@category 根据名字查询商品分类value
	 *@author 邱磊
	 *@date 2015年6月17日 下午9:07:15
	 *@param name
	 *@return
	 */
	public DicSys getDicSysByName(String name) {
		String hql = "from DicSys t where t.classifyCode='"+Constants.DRUG_CAT+"' and t.dicName='"
				+ name + "'";
		return (DicSys) dao.getUniqueResult(hql);
	}
	/**
	 * 
	 *@category 查询所有的商品
	 *@author 邱磊
	 *@date 2015年6月17日 下午9:07:54
	 *@return
	 */
	@SuppressWarnings("unchecked")
	public List<BaseGoods> getBaseGoodsList() {
		String hql = "from BaseGoods";
		List<String> param = new ArrayList<String>();
		return (List<BaseGoods>) dao.getList(hql, param);
	}
	/**
	 * 
	 *@category 查询所有的药品
	 *@author 邱磊
	 *@date 2015年6月17日 下午9:08:13
	 *@return
	 */
	@SuppressWarnings("unchecked")
	public List<Object[]> getStdDrugList() {
		String sql = "select * from view_drug";
		List<String> param = new ArrayList<String>();
		return (List<Object[]>) dao.getSqlList(sql, param);
	}
	@SuppressWarnings("rawtypes")
	public Object[] getStdDrugByCode(String drugCode) {
		String sql = "select * from view_drug where drug_code=?";
		List<String> param = new ArrayList<String>();
		param.add(0, drugCode);
		List list = dao.getSqlList(sql, param);
		if (null != list && list.size() > 0){
			return (Object[]) list.get(0);
		}
		return null;
	}
	/**
	 * 
	 *@category 查询所有的项目
	 *@author 邱磊
	 *@date 2015年6月17日 下午9:08:27
	 *@return
	 */
	@SuppressWarnings("unchecked")
	public List<BidProject> getBidProjectList() {
		String hql = "from BidProject";
		List<String> param = new ArrayList<String>();
		return (List<BidProject>) dao.getList(hql, param);
	}
	/**
	 * 
	 *@category 查询所有的商品分类
	 *@author 邱磊
	 *@date 2015年6月17日 下午9:08:44
	 *@return
	 */
	@SuppressWarnings("unchecked")
	public List<DicSys> getDicSysList() {
		String hql = "from DicSys t where t.classifyCode='115'";
		List<String> param = new ArrayList<String>();
		return (List<DicSys>) dao.getList(hql, param);
	}

	/**
	 * @category author 邱磊
	 * @date 2015年6月14日 下午9:06:18
	 * @param data
	 */
	private void writeLog(BaseGoods data) {
		String sql = "INSERT INTO base_goods_item_log ( log_id, goods_code, area_id, "
				+ "area_name, ar_shortname, ar_fullname, proj_id, proj_name, "
				+ "proj_starttime, proj_endtime, proj_status, qualityspec_id, "
				+ "drug_code, qualityspec_name, qualityspec_name_spel, "
				+ "qualityspec_name_eng, form_name, spec_name, qualityspec_status, "
				+ "bus_name, factor, unit_name, material_name, prod_compcode, "
				+ "prod_compname, prod_compname_spel, prod_compstatus, cat_comp_code, "
				+ "cat_comp_name, cat_comp_name_spel, cat_comp_status, use_range, "
				+ "drug_attr, goods_source, pattern_type, is_healthcare, is_agriculture, "
				+ "is_lowprice, is_smallprice, is_smallsource, is_longtime_plan, "
				+ "drug_status, drug_cat, pro_price_limit, retail_price_limit, "
				+ "hang_price, ref_price, healthcare_price, pic_url, pic_short_url, "
				+ "`status`, upd_user, upd_datetime ) SELECT UUID() log_id, goods_code, "
				+ "area_id, area_name, ar_shortname, ar_fullname, proj_id, proj_name, "
				+ "proj_starttime, proj_endtime, proj_status, qualityspec_id, drug_code, "
				+ "qualityspec_name, qualityspec_name_spel, qualityspec_name_eng, "
				+ "form_name, spec_name, qualityspec_status, bus_name, factor, unit_name, "
				+ "material_name, prod_compcode, prod_compname, prod_compname_spel, "
				+ "prod_compstatus, cat_comp_code, cat_comp_name, cat_comp_name_spel, "
				+ "cat_comp_status, use_range, drug_attr, goods_source, pattern_type, "
				+ "is_healthcare, is_agriculture, is_lowprice, is_smallprice, is_smallsource, "
				+ "is_longtime_plan, drug_status, drug_cat, pro_price_limit, retail_price_limit, "
				+ "hang_price, ref_price, healthcare_price, pic_url, pic_short_url, "
				+ "`status`, upd_user, upd_datetime FROM base_goods_item "
				+ "WHERE goods_code = :goodsCode";
		SQLQuery query = getSession().createSQLQuery(sql);
		query.setString("goodsCode", data.getGoodsCode());
		query.executeUpdate();
		getSession().flush();
	}

	/**
	 * 
	 * @category 获取生产企业设置商品配送关系列表
	 * @author 邱磊
	 * @date 2015年6月17日 上午11:09:48
	 * @param page
	 * @return
	 */
	public Pagination getProDelDrugDataList(Pagination page) {
		StringBuffer sql = new StringBuffer()
		.append(" SELECT a.goods_code goodsCode,")
		.append(" 	a.drug_code drugCode,")
		.append(" 	a.qualityspec_name qualityspecName,")
		.append(" 	a.form_name formName,")
		.append(" 	a.spec_name specName,")
		.append(" 	a.factor,")
		.append(" 	a.unit_name unitName,")
		.append(" 	a.proj_id projId,")
		.append(" 	a.proj_name projName,")
		.append(" 	a.material_name materialName,")
		.append(" 	a.area_id goodsArea,")
		.append(" 	a.pro_price_limit proPriceLimit")
		.append(" FROM")
		.append(" 	base_goods_item a")
		.append(" WHERE")
		.append(" 	  a.STATUS = 1")
		.append(" AND a.qualityspec_status = 1 and a.drug_status = 1")
		.append(" AND a.proj_status = 1")
		.append(" AND SYSDATE() BETWEEN a.proj_starttime and a.proj_endtime")
		.append(" AND a.prod_compcode=:compCode");
		Map<String, String> map = page.getConditions();
		Map<String, Object> params = new HashMap<String, Object>();
		if (null != map && map.size() > 0) {
			if (StringUtils.isNotBlank(map.get("compCode"))) {
				params.put("compCode", map.get("compCode").trim());
			}
			if (StringUtils.isNotBlank(map.get("drugCode"))) {
				sql.append(" and a.drug_code=:drugCode");
				params.put("drugCode", map.get("drugCode").trim());
			}
			if (StringUtils.isNotBlank(map.get("drugName"))) {
				if (PinYin.isPinYin(map.get("drugName").trim())){
					sql.append(" and a.qualityspec_name_spel like :drugName");
					params.put("drugName", "%" + map.get("drugName").trim().toUpperCase() + "%");
				} else {
					sql.append(" and a.qualityspec_name like :drugName");
					params.put("drugName", "%" + map.get("drugName").trim() + "%");
				}
				
			}
			if (StringUtils.isNotBlank(map.get("projId"))) {
				sql.append(" and a.proj_name like :projId");
				params.put("projId", "%" + map.get("projId").trim() + "%");
			}
			if (StringUtils.isNotBlank(map.get("goodsCode"))) {
				sql.append(" and a.goods_code=:goodsCode");
				params.put("goodsCode", map.get("goodsCode").trim());
			}
		}
		page = dao.getPageMapList(sql.toString(), params, page);
		return page;
	}

	/**
	 * 
	 * @category 根据区域设置配送企业列表
	 * @author 邱磊
	 * @date 2015年6月17日 下午4:22:16
	 * @return
	 */
	public Pagination getBaseGoodsDelByAreaList(Pagination page) {

		StringBuffer sql = new StringBuffer()
		.append("SELECT distinct b.comp_id compId,b.comp_code compCode, b.comp_name compName,b.org_code orgCode, c.contact_name contactName, c.contact_telephone contactTelephone  ")
		.append("  FROM bid_projcompany a join std_compinfo b on a.comp_code=b.comp_code and b.status=1  AND  b.comp_type = 2   ")
		.append("  LEFT JOIN std_compinfo_detail c ON c.comp_id=b.comp_id  ")
		.append("  WHERE a.status = 1 and SYSDATE() between a.proj_starttime and a.proj_endtime")
		.append("  AND a.comp_code NOT IN ( SELECT d.del_comp_code FROM base_delrelationship d WHERE d.area_code=:areaCode AND d.goods_code=:goodsCode)");
		Map<String, String> map = page.getConditions();
		Map<String, Object> params = new HashMap<String, Object>();
		if (null != map && map.size() > 0) {
			if (StringUtils.isNotBlank(map.get("projID"))) {
				sql.append(" and a.proj_id = :projId");
				params.put("projId", map.get("projID").trim());
			}
			if (StringUtils.isNotBlank(map.get("areaId"))) {
				params.put("areaCode", map.get("areaId").trim());
			}
			if (StringUtils.isNotBlank(map.get("goodsCode"))) {
				params.put("goodsCode", map.get("goodsCode").trim());
			}
			
			if (StringUtils.isNotBlank(map.get("compCode"))) {
				sql.append(" and b.comp_code=:compCode");
				params.put("compCode", map.get("compCode").trim());
			}
			if (StringUtils.isNotBlank(map.get("compName"))) {
				sql.append(" and (b.comp_name like :compName or b.comp_spel_code like :compSpelCode)");
				params.put("compName", "%"+map.get("compName").trim()+"%");
				params.put("compSpelCode", "%"+map.get("compName").trim().toUpperCase()+"%");
			}
		}
		sql.append(" order by b.comp_code");
		page = dao.getSqlPageMapList(sql.toString(), params, page);
		return page;
	}
	
	/**
	 * 
	 * @category 根据区域设置配送企业列表
	 * @author 邱磊
	 * @date 2015年6月17日 下午4:22:16
	 * @return
	 */
	@Override
	public Pagination getBaseGoodsDelByLinxuan(Pagination page) {

		StringBuffer sql = new StringBuffer()
		.append("SELECT distinct b.comp_id compId,b.comp_code compCode, b.comp_name compName,b.org_code orgCode, c.contact_name contactName, c.contact_telephone contactTelephone,a.del_range delRange  ")
		.append("  FROM base_del_area_rel a join std_compinfo b on a.del_comp_code=b.comp_code and b.status=1  AND  b.comp_type = 2  and a.area_code like :province ")
		.append("  LEFT JOIN std_compinfo_detail c ON c.comp_id=b.comp_id  ")
		.append("  WHERE a.status = 2 and a.area_code = :areaCode ")
		.append("  AND a.del_comp_code NOT IN ( SELECT d.del_comp_code FROM base_delrelationship d WHERE d.area_code=:areaCode AND d.goods_code=:goodsCode)");
		Map<String, String> map = page.getConditions();
		Map<String, Object> params = new HashMap<String, Object>();
		params.put("province", province.substring(0, 2)+"%");
		if (null != map && map.size() > 0) {
			if (StringUtils.isNotBlank(map.get("areaId"))) {
				params.put("areaCode", map.get("areaId").trim());
			}
			if (StringUtils.isNotBlank(map.get("goodsCode"))) {
				params.put("goodsCode", map.get("goodsCode").trim());
			}
			
			if (StringUtils.isNotBlank(map.get("compCode"))) {
				sql.append(" and b.comp_code=:compCode");
				params.put("compCode", map.get("compCode").trim());
			}
			if (StringUtils.isNotBlank(map.get("compName"))) {
				sql.append(" and (b.comp_name like :compName or b.comp_spel_code like :compSpelCode)");
				params.put("compName", "%"+map.get("compName").trim()+"%");
				params.put("compSpelCode", "%"+map.get("compName").trim().toUpperCase()+"%");
			}
		}
		sql.append(" order by b.comp_code");
		page = dao.getSqlPageMapList(sql.toString(), params, page);
		return page;
	}

	/**
	 * 
	 * @category 根据区域设置配送企业
	 * @author 邱磊
	 * @date 2015年6月17日 下午4:58:22
	 * @param list
	 * @return
	 */
	public void setDelByArea(List<BaseDelrelation> list, Map<String, String> map) {
		if (null != list && list.size() > 0) {
			Map<String, Object> area = areaDao.getAreaByAreaId(map.get("areaCode"));
			Map<String, Object> goods = this.getGoodsByCode(map.get("goodsCode"));
			for (int i = 0; i < list.size(); i++) {
				BaseDelrelation data = list.get(i);
				Map<String, Object> delArea = purConfigManager.getBaseDelAreaByCode(area.get("area_id").toString(), data.getDelCompCode());
				String uuid = UUID.randomUUID().toString();
				StringBuffer sql = new StringBuffer()
				.append("insert into base_delrelationship (del_rel_id,goods_code,prod_comp_code,prod_comp_name,prod_comp_name_spel,prod_comp_status,")
				.append(" area_code,area_name,ar_shortname,ar_fullname,del_comp_code,del_comp_name,del_comp_name_spel,del_comp_status,hosp_code,")
				.append(" hosp_name,hosp_spel_code,hosp_status,del_range,status,upd_user,upd_datetime)")
				.append(" select :delRelId,:goodsCode,:prodCompCode,:prodCompName,:prodCompSpel,:prodCompStatus,:areaCode,:areaName,:arShortName,")
				.append(" :arFullName,a.comp_code,a.comp_name,a.comp_spel_code,a.status,:hospCode,:hospName,:hospSpelCode,:hospStatus,:delRange,")
				.append(" 0,:userId,SYSDATE() from std_compinfo a where a.comp_code=:compCode");
				Map<String, Object> params = new HashMap<String, Object>();
				params.put("delRelId", uuid);
				params.put("goodsCode", goods.get("goods_code"));
				params.put("prodCompCode", goods.get("prod_compcode"));
				params.put("prodCompName", goods.get("prod_compname"));
				params.put("prodCompSpel", goods.get("prod_compname_spel"));
				params.put("prodCompStatus", goods.get("prod_compstatus"));
				params.put("areaCode", area.get("area_id"));
				params.put("areaName", area.get("area_name"));
				params.put("arShortName", area.get("ar_shortname"));
				params.put("arFullName", area.get("ar_fullname"));
				params.put("hospCode", null);
				params.put("hospName", null);
				params.put("hospSpelCode", null);
				params.put("hospStatus", null);
				params.put("delRange", delArea == null ? Constants.DEL_RANGE_ALL : delArea.get("del_range"));
				params.put("userId", map.get("userId"));
				params.put("compCode", data.getDelCompCode());
				dao.executeSql(sql.toString(), params);
				StringBuffer logsql = new StringBuffer()
				.append("insert into base_delrelationship_log")
				.append(" select UUID(),t.* from base_delrelationship t where t.del_rel_id= :delRelId");
				Map<String, Object> logparams = new HashMap<String, Object>();
				logparams.put("delRelId", uuid);
				dao.executeSql(logsql.toString(), logparams);
			}
		}
	}
	
	public void setDelByComp(List<BaseDelrelation> list, Map<String, String> map) {
		if (null != list && list.size() > 0) {
			Map<String, Object> comp = compDao.getCompinfoByCode( list.get(0).getDelCompCode());
			Map<String, Object> goods = this.getGoodsByCode(list.get(0).getGoodsCode());
			for (int i = 0; i < list.size(); i++) {
				BaseDelrelation data = list.get(i);
				String uuid = UUID.randomUUID().toString();
				StringBuffer sql = new StringBuffer()
				.append("insert into base_delrelationship (del_rel_id,goods_code,prod_comp_code,prod_comp_name,prod_comp_name_spel,prod_comp_status,")
				.append(" area_code,area_name,ar_shortname,ar_fullname,del_comp_code,del_comp_name,del_comp_name_spel,del_comp_status,hosp_code,")
				.append(" hosp_name,hosp_spel_code,hosp_status,del_range,status,upd_user,upd_datetime)")
				.append(" select :delRelId,:goodsCode,:prodCompCode,:prodCompName,:prodCompSpel,:prodCompStatus,a.area_id,a.area_name,a.ar_shortname,")
				.append(" a.ar_fullname,:compCode,:compName,:compNameSpel,:compStatus,:hospCode,:hospName,:hospSpelCode,:hospStatus,:delRange,")
				.append(" 0,:userId,SYSDATE() from dic_area a where a.area_id=:areaCode");
				Map<String, Object> params = new HashMap<String, Object>();
				params.put("delRelId", uuid);
				params.put("goodsCode", goods.get("goods_code"));
				params.put("prodCompCode", goods.get("prod_compcode"));
				params.put("prodCompName", goods.get("prod_compname"));
				params.put("prodCompSpel", goods.get("prod_compname_spel"));
				params.put("prodCompStatus", goods.get("prod_compstatus"));
				params.put("compCode", comp.get("comp_code"));
				params.put("compName", comp.get("comp_name"));
				params.put("compNameSpel", comp.get("comp_spel_code"));
				params.put("compStatus", comp.get("status"));
				params.put("hospCode", null);
				params.put("hospName", null);
				params.put("hospSpelCode", null);
				params.put("hospStatus", null);
				params.put("delRange", StringUtils.isNotBlank(data.getDelRange())?data.getDelRange():Constants.DEL_RANGE_ALL);
				params.put("userId", map.get("userId"));
				params.put("areaCode", data.getAreaCode());
				dao.executeSql(sql.toString(), params);
				StringBuffer logsql = new StringBuffer()
				.append("insert into base_delrelationship_log")
				.append(" select UUID(),t.* from base_delrelationship t where t.del_rel_id= :delRelId");
				Map<String, Object> logparams = new HashMap<String, Object>();
				logparams.put("delRelId", uuid);
				dao.executeSql(logsql.toString(), logparams);
			}
		}
	}
	

	public void removeDel(Pagination page){
		Session session =getSession();
		List<String> delRelIdes = new ArrayList<String>();
		String[] str = page.getConditions().get("param").split(",");
		for(int i=0,temp=str.length; i<temp; i++){
			delRelIdes.add(str[i]);
		}
		StringBuffer sbLog = new StringBuffer()
		.append(" insert into base_delrelationship_log  ")
		.append("(      log_id,del_rel_id,goods_code,prod_comp_code,prod_comp_name,prod_comp_name_spel,prod_comp_status,area_code,area_name,ar_shortname,ar_fullname,del_comp_code,del_comp_name,del_comp_name_spel,del_comp_status,hosp_code,hosp_name,hosp_spel_code,hosp_status,del_range,status,upd_user,upd_datetime)")
		.append("select uuid(),del_rel_id,goods_code,prod_comp_code,prod_comp_name,prod_comp_name_spel,prod_comp_status,area_code,area_name,ar_shortname,ar_fullname,del_comp_code,del_comp_name,del_comp_name_spel,del_comp_status,hosp_code,hosp_name,hosp_spel_code,hosp_status,del_range,:status,:updUser,:updDatetime  ")
		.append(" FROM base_delrelationship where del_rel_id in (:list) ");
		SQLQuery queryInsert = session.createSQLQuery(sbLog.toString());
		queryInsert.setParameter("status", page.getConditions().get("status"))
		.setParameter("updUser", page.getConditions().get("userId"))
		.setParameter("updDatetime", new Date())
		.setParameterList("list",delRelIdes)
		.executeUpdate();
	
		StringBuffer sb = new StringBuffer()
		.append(" delete from base_delrelationship where del_rel_id in(:list) ");
		SQLQuery query = session.createSQLQuery(sb.toString());
		query.setParameterList("list",delRelIdes)
		.executeUpdate();
		session.flush();
		session.clear();
	}
	/**
	 * 
	 *@category 解除配送关系时更新医疗机构商品目录配送关系
	 *@author 邱磊
	 *@date 2015年7月8日 下午3:25:39
	 *@param form
	 */
	@SuppressWarnings("unused")
	private void updatePurHospCatalog(BaseDelrelationForm form){
		StringBuffer sql = new StringBuffer()
		.append("UPDATE pur_hospcatalog set del_comp_id = null,upd_datetime=SYSDATE(),upd_user=:updUser")
		.append(" where goods_code=:goodsCode")
		.append(" and hosp_id in (select hosp_id from std_hospinfo where area_id=:areaId)");
		Map<String, Object> params = new HashMap<String, Object>();
		params.put("updUser", form.getUpdUser());
		params.put("goodsCode", form.getGoodsCode());
		params.put("areaId", form.getAreaCode());
		dao.executeSql(sql.toString(), params);
		sql = new StringBuffer()
		.append(" INSERT into pur_hospcatalog_log")
		.append(" select UUID(),t.hosp_pro_cat_id,t.hosp_id,t.goods_code,t.del_comp_id,t.pro_price,t.pro_count,")
		.append(" t.status,t.bak_pur_count,1,t.upd_datetime,t.upd_user from pur_hospcatalog t where t.goods_code=:goodsCode")
		.append(" and hosp_id in (select hosp_id from std_hospinfo where area_id=:areaCode)");
		params  = new HashMap<String, Object>();
		params.put("goodsCode", form.getGoodsCode());
		params.put("areaCode", form.getAreaCode());
		dao.executeSql(sql.toString(), params);
	}
	/**
	 * 
	 *@category 解除配送关系时更新医疗机构商品目录配送关系
	 *@author 邱磊
	 *@date 2015年7月8日 下午3:25:39
	 *@param form
	 */
	@SuppressWarnings("unused")
	private void updatePurPlan(BaseDelrelationForm form){
		StringBuffer sql = new StringBuffer()
		.append("UPDATE pur_purplan set del_comp_id=NULL")
		.append(" where goods_code=:goodsCode")
		.append(" and hosp_id in (select hosp_id from std_hospinfo where area_id=:areaId)");
		Map<String, Object> params = new HashMap<String, Object>();
		params.put("goodsCode", form.getGoodsCode());
		params.put("areaId", form.getAreaCode());
		dao.executeSql(sql.toString(), params);
	}
	
	@Value("#{sysProp['system.province']}")
    private String province;//省份
	/**
	 * 
	 * @category 根据企业设置配送区域的区域
	 * @author 邱磊
	 * @date 2015年6月17日 下午8:55:56
	 * @param page
	 * @return
	 */
	public Pagination getAreaList(Pagination page) {
		StringBuffer sql = new StringBuffer()
		.append("SELECT a.area_id,a.area_name,a.father_id,c.area_name father_name,")
		.append("(select count(*) from base_delrelationship t")
		.append(" where t.area_code=a.area_id and t.goods_code = :goodsCode) as delCount")
		.append(" FROM dic_area a LEFT JOIN dic_area c ON c.area_id=a.father_id")
		.append(" WHERE a.status=1 AND  a.area_id LIKE :goodsArea AND a.area_id NOT LIKE '%00'");
		Map<String, Object> params = new HashMap<String, Object>();
		Map<String, String> map = page.getConditions();
		params.put("goodsCode", map.get("goodsCode"));
		params.put("goodsArea", AreaUtil.getAreaPrefix(map.get("goodsArea")) + "%");
		if (StringUtils.isNotBlank(map.get("areaCode"))) {
			sql.append(" and a.area_id = :areaId");
			params.put("areaId", map.get("areaCode"));
		}
		if (StringUtils.isNotBlank(map.get("fatherCode"))) {
			sql.append(" and a.father_id = :fatherCode");
			params.put("fatherCode", map.get("fatherCode"));
		}
		sql.append(" order by a.area_id");
		page = dao.getSqlPageMapList(sql.toString(), params, page);
		return page;
	}
	
	/**
	 * 
	 * @category 根据企业设置配送区域的区域
	 * @author 邱磊
	 * @date 2015年6月17日 下午8:55:56
	 * @param page
	 * @return
	 */
	@Override
	public Pagination getAreaListByLinxuan(Pagination page) {
		StringBuffer sql = new StringBuffer()
		.append("SELECT a.area_id,a.area_name,a.father_id,c.area_name father_name,")
		.append("(select count(*) from base_delrelationship t")
		.append(" where t.area_code=a.area_id and t.goods_code = :goodsCode) as delCount,del_range ")
		.append(" FROM base_del_area_rel p left join dic_area a on a.area_id = p.area_code LEFT JOIN dic_area c ON c.area_id=a.father_id")
		.append(" WHERE a.status=1 AND  a.area_id LIKE :goodsArea AND a.area_id NOT LIKE '%00' and p.status = 2 ");
		Map<String, Object> params = new HashMap<String, Object>();
		Map<String, String> map = page.getConditions();
		params.put("goodsCode", map.get("goodsCode"));
		params.put("goodsArea", AreaUtil.getAreaPrefix(map.get("goodsArea")) + "%");
		if (StringUtils.isNotBlank(map.get("areaCode"))) {
			sql.append(" and a.area_id = :areaId");
			params.put("areaId", map.get("areaCode"));
		}
		if (StringUtils.isNotBlank(map.get("fatherCode"))) {
			sql.append(" and a.father_id = :fatherCode");
			params.put("fatherCode", map.get("fatherCode"));
		}
		sql.append(" order by a.area_id");
		page = dao.getSqlPageMapList(sql.toString(), params, page);
		return page;
	}
	/**
	 * 
	 *@category 查询当前生产企业的所有药品配送关系
	 *@author 邱磊
	 *@date 2015年6月18日 上午9:59:52
	 *@param page
	 *@return
	 */
	public Pagination getBaseDelByCompList(Pagination page){
		StringBuffer sql = new StringBuffer()
		.append("  SELECT a.del_rel_id delRelId,")
		.append(" 	a.goods_code goodsCode,")
		.append(" 	b.qualityspec_name qualityspecName,")
		.append(" 	b.drug_code drugCode,")
		.append(" 	a.del_comp_code delCompCode,")
		.append(" 	a.del_comp_name delCompName,")
		.append(" 	a.area_name areaName,")
		.append(" 	b.form_name formName,")
		.append(" 	b.spec_name specName,")
		.append(" 	b.factor,")
		.append(" 	b.unit_name unitName,")
		.append(" 	b.drug_cat drugCat,")
		.append(" 	a.STATUS,")
		.append(" 	b.proj_id projId,")
		.append(" 	b.proj_name projName,")
		.append(" 	b.material_name materialName,")
		.append(" 	b.pro_price_limit proPriceLimit")
		.append(" FROM base_delrelationship a")
		.append(" LEFT JOIN base_goods_item b ON a.goods_code = b.goods_code")
		.append(" AND b. STATUS = 1")
		.append(" AND b.qualityspec_status = 1")
		.append(" AND b.proj_status = 1")
		.append(" AND SYSDATE() BETWEEN b.proj_starttime")
		.append(" AND b.proj_endtime")
		.append(" where a.status=1 and a.prod_comp_code =:prodCompCode ");
		Map<String, String> map = page.getConditions();
		Map<String, Object> params = new HashMap<String, Object>();
		
		params.put("prodCompCode", map.get("compCode").trim());
		
		if (null != map && map.size() > 0){
			if (StringUtils.isNotBlank(map.get("goodsCode"))){
				sql.append(" and a.goods_code=:goodsCode");
				params.put("goodsCode", map.get("goodsCode").trim());
			}
			
			if (StringUtils.isNotBlank(map.get("drugCode"))){
				sql.append(" and b.drug_code=:drugCode");
				params.put("drugCode", map.get("drugCode").trim());
			}
			if (StringUtils.isNotBlank(map.get("drugName"))){
				sql.append(" and (b.qualityspec_name like :drugName or b.qualityspec_name_spel like :drugNameSpel)");
				params.put("drugName", "%"+map.get("drugName").trim()+"%");
				params.put("drugNameSpel", "%"+map.get("drugName").trim().toUpperCase()+"%");
			}
			if (StringUtils.isNotBlank(map.get("projName"))){
				sql.append(" and b.proj_name like :projName");
				params.put("projName", "%" + map.get("projName").trim() + "%");
			}
//			if (StringUtils.isNotBlank(map.get("areaCode"))){
//				sql.append(" and a.area_code like :areaCode");
//				params.put("areaCode", map.get("areaCode").trim()+"%");
//			}
			if (StringUtils.isNotBlank(map.get("compName"))){
				sql.append(" and (a.del_comp_name like :compName or a.del_comp_name_spel like :compSpelCode)");
				params.put("compName", "%"+map.get("compName").trim()+"%");
				params.put("compSpelCode", "%"+map.get("compName").trim().toUpperCase()+"%");
			}
		}
		//sql.append(" ORDER BY a.goods_code,a.area_code");
		
	
		return dao.getPageMapList(sql.toString(), params, page);
	}
	
	@SuppressWarnings("unchecked")
	public Pagination getBaseDelCentreByCompList(Pagination page){
		StringBuffer sql = new StringBuffer()
		.append("select a.del_rel_id,b.proj_name,a.goods_code,b.drug_code,b.qualityspec_name,b.form_name,b.spec_name,")
		.append("b.factor,b.unit_name,b.material_name,b.prod_compname,b.prod_compcode,a.area_name,a.del_comp_name,")
		.append("a.del_comp_code,b.pro_price_limit");
		
		StringBuffer sqlCount = new StringBuffer("select count(*) ");
		
		StringBuffer sqlEnd = new StringBuffer().append(" from base_delrelationship a ")
		.append(" left join base_goods_item b on a.goods_code=b.goods_code")
		.append(" left join dic_area e on a.area_code=e.area_id")
		.append("  where a.status=1 and b.status = 1 and b.proj_status = 1 ")
		.append("  and b.proj_starttime<=SYSDATE() and b.proj_endtime>=SYSDATE()");
		
		sqlCount.append(sqlEnd);
		sql.append(sqlEnd);
		
		Map<String, Object> params = new HashMap<String, Object>();
		Map<String, String> map = page.getConditions();
		if (null != map && map.size() > 0){
			if (StringUtils.isNotBlank(map.get("projId"))){
				sql.append(" and b.proj_id=:projId");
				sqlCount.append(" and b.proj_id=:projId");
				params.put("projId", map.get("projId").trim());
			}
			if (StringUtils.isNotBlank(map.get("projectName"))){
				sql.append(" and b.proj_name like :projectName");
				sqlCount.append(" and b.proj_name like :projectName");
				params.put("projectName","%" + map.get("projectName").trim() + "%");
			}
			if (StringUtils.isNotBlank(map.get("goodsCode"))){
				sql.append(" and a.goods_code=:goodsCode");
				sqlCount.append(" and a.goods_code=:goodsCode");
				params.put("goodsCode", map.get("goodsCode").trim());
			}
			if (StringUtils.isNotBlank(map.get("drugCode"))){
				sql.append(" and b.drug_code=:drugCode");
				sqlCount.append(" and b.drug_code=:drugCode");
				params.put("drugCode", map.get("drugCode").trim());
			}
			if (StringUtils.isNotBlank(map.get("drugName"))){
				sql.append(" and (b.qualityspec_name like :drugName or b.qualityspec_name_spel like :drugName)");
				sqlCount.append(" and (b.qualityspec_name like :drugName or b.qualityspec_name_spel like :drugName)");
				params.put("drugName", "%" + map.get("drugName") + "%");
			}
			if (StringUtils.isNotBlank(map.get("compName"))){
				sql.append(" and (b.prod_compname like :compName or b.prod_compname_spel like :compName)");
				sqlCount.append(" and (b.prod_compname like :compName or b.prod_compname_spel like :compName)");
				params.put("compName", "%"+map.get("compName").trim()+"%");
			}
			if (StringUtils.isNotBlank(map.get("areaCode"))){
				sql.append(" and a.area_code = :areaCode");
				sqlCount.append(" and a.area_code = :areaCode");
				params.put("areaCode", map.get("areaCode").trim());
			}
			if (StringUtils.isNotBlank(map.get("fatherCode"))){
				sql.append(" and e.father_id = :fatherCode");
				sqlCount.append(" and e.father_id = :fatherCode");
				params.put("fatherCode", map.get("fatherCode").trim());
			}
			if (StringUtils.isNotBlank(map.get("delCompName"))){
				sql.append(" and (a.del_comp_name like :delName or a.del_comp_name_spel like :delName)");
				sqlCount.append(" and (a.del_comp_name like :delName or a.del_comp_name_spel like :delName)");
				params.put("delName", "%"+map.get("delCompName").trim()+"%");
			}
		}
		
        Query query = getSession().createSQLQuery(sql.toString());
		if (params != null) {
			Iterator<?> list = params.entrySet().iterator();
			while (list.hasNext()) {
				Map.Entry<String, ?> entry = (Map.Entry<String, ?>) list.next();
				query.setParameter((String) entry.getKey(), entry.getValue());
			}
		}
		if (page.getMaxResults() > 0) {			
			Query countQuery = getSession().createSQLQuery(sqlCount.toString());
			if (params != null) {
				Iterator<?> list = params.entrySet().iterator();
				while (list.hasNext()) {
					Map.Entry<String, ?> entry = (Map.Entry<String, ?>) list.next();
					countQuery.setParameter((String) entry.getKey(), entry.getValue());
				}
			}
			BigInteger records = (BigInteger) countQuery.uniqueResult();
			page.setRecords(records.longValue());
		}
		query.setFirstResult(page.getFirstResult());
		query.setMaxResults(page.getMaxResults());

		List<?> list = query.list();
		page.setRows(list);
		return page;
		
	}
	/**
	 * 
	 *@category 查询配送关系日志列表
	 *@author 邱磊
	 *@date 2015年6月18日 上午11:21:20
	 *@param page
	 *@return
	 */
	public Pagination getBaseDelByCompLogList(Pagination page){
		StringBuffer sql = new StringBuffer()
		.append("SELECT a.del_rel_id,a.goods_code,c.drug_name,c.drug_code,d.comp_name,e.ar_fullname,")
		.append("c.form_name,c.spec_name,c.factor,c.unit,b.drug_cat,a.`status` ")
		.append(" from base_delrelation_log a left join base_goods b on a.goods_code=b.goods_code")
		.append(" left join view_drug c on c.drug_id=b.drug_id  left join std_compinfo d on d.comp_id=a.del_comp_id")
		.append(" left join dic_area e on e.area_id=a.area_code")
		.append(" where a.del_rel_id=:delRelId");
		Map<String, String> map = page.getConditions();
		Map<String, Object> params = new HashMap<String, Object>();
		params.put("delRelId", map.get("delRelId").trim());
		if (null != map && map.size() > 0){
			if (StringUtils.isNotBlank(map.get("startDate"))){
				sql.append(" and b.upd_datetime >:startDate");
				params.put("startDate", map.get("startDate").trim());
			}
			if (StringUtils.isNotBlank(map.get("endDate"))){
				sql.append(" and b.upd_datetime <:endDate");
				params.put("endDate", map.get("endDate").trim());
			}
		}
		sql.append("  order by a.upd_datetime desc");
		return dao.getSqlPageList(sql.toString(), params, page);
	}
	public Pagination getSetDelComp(Pagination page){
		StringBuffer sql = new StringBuffer()
		.append("select a.del_rel_id delRelId,a.del_comp_code delCompCode,a.del_comp_name delCompName,b.org_code,c.contact_name,c.contact_telephone,a.status from base_delrelationship a ")
		.append(" left join std_compinfo b on a.del_comp_code=b.comp_code")
		.append(" left join std_compinfo_detail c on b.comp_id=c.comp_id")
		.append(" where a.area_code=:areaCode and a.goods_code=:goodsCode ");
		Map<String, Object> params = new HashMap<String, Object>();
		params.put("areaCode", page.getConditions().get("areaCode"));
		params.put("goodsCode", page.getConditions().get("goodsCode"));
		Map<String, String> map = page.getConditions();
		if (StringUtils.isNotBlank(map.get("compCode"))){
			sql.append(" and a.del_comp_code=:compCode");
			params.put("compCode", map.get("compCode"));
		}
		if (StringUtils.isNotBlank(map.get("compName"))){
			sql.append(" and (a.del_comp_name like :compName or a.del_comp_name_spel like :compSpelCode)");
			params.put("compName", "%"+map.get("compName").trim()+"%");
			params.put("compSpelCode", "%"+map.get("compName").trim().toUpperCase()+"%");
		}
		if (StringUtils.isNotBlank(map.get("status"))){
			sql.append(" and a.status=:status");
			params.put("status", map.get("status"));
		}
		sql.append(" order by b.comp_code");
		return dao.getSqlPageList(sql.toString(), params, page);
	}
	
	public Pagination getBaseDelerationLogList(Pagination page){
		StringBuffer sql = new StringBuffer()
		.append("SELECT t.del_comp_code compCode,t.del_comp_name compName,a.org_code orgCode,b.contact_name contactName,b.contact_telephone contactPhone,t.status,t.upd_datetime updTime")
		.append(" from base_delrelationship_log t ")
		.append(" left join std_compinfo a on t.del_comp_code=a.comp_code")
		.append(" left join std_compinfo_detail b on a.comp_id=b.comp_id")
		.append("  where t.goods_code=:goodsCode  and t.area_code=:areaCode");
		Map<String, Object> params = new HashMap<String, Object>();
		params.put("goodsCode", page.getConditions().get("goodsCode"));
		params.put("areaCode", page.getConditions().get("areaCode"));
		if (StringUtils.isNotBlank(page.getConditions().get("compCode"))){
			sql.append(" and t.del_comp_code=:compCode");
			params.put("compCode", page.getConditions().get("compCode").trim());
		}
		if (StringUtils.isNotBlank(page.getConditions().get("compName"))){
			sql.append(" and (t.del_comp_name like :compName or t.del_comp_name_spel like :compNameSpel)");
			params.put("compName", "%"+page.getConditions().get("compName").trim()+"%");
			params.put("compNameSpel", "%"+page.getConditions().get("compName").trim().toUpperCase()+"%");
		}
		if (StringUtils.isNotBlank(page.getConditions().get("startDate"))){
			sql.append(" and t.upd_datetime >=:startDate");
			params.put("startDate", page.getConditions().get("startDate").trim()+" 00:00:00");
		}
		if (StringUtils.isNotBlank(page.getConditions().get("endDate"))){
			sql.append(" and t.upd_datetime <=:endDate");
			params.put("endDate", page.getConditions().get("endDate").trim()+" 23:59:59");
		}
		sql.append(" order by t.upd_datetime desc");
		return dao.getSqlPageMapList(sql.toString(), params, page);
	}
	public Pagination getDelComp(Pagination page){
		StringBuffer sql = new StringBuffer()
		.append("SELECT DISTINCT a.comp_code compCode,b.comp_name compName, ")
		.append(" b.org_code orgCode,c.contact_name contactName,c.contact_telephone contactPhone,")
		.append("(select count(*) from base_delrelationship p where p.del_comp_code=a.comp_code and p.goods_code=:goodsCode) as areaCount ")
		.append(" FROM bid_projcompany a ")
		.append(" LEFT JOIN std_compinfo b ON a.comp_code=b.comp_code ")
		.append(" LEFT JOIN std_compinfo_detail c ON b.comp_id=c.comp_id ")
		.append(" WHERE a.status=1 AND b.status=1 AND a.proj_id = :projId");
		Map<String, Object> params = new HashMap<String, Object>();
		params.put("goodsCode", page.getConditions().get("goodsCode"));
		params.put("projId", page.getConditions().get("projId"));
		if (StringUtils.isNotBlank(page.getConditions().get("compCode"))){
			sql.append(" and a.comp_code=:compCode");
			params.put("compCode", page.getConditions().get("compCode").trim());
		}
		if (StringUtils.isNotBlank(page.getConditions().get("compName"))){
			sql.append(" and (b.comp_name like :compName or b.comp_spel_code like :compSpelCode)");
			params.put("compName", "%"+page.getConditions().get("compName").trim()+"%");
			params.put("compSpelCode", "%"+page.getConditions().get("compName").trim().toUpperCase()+"%");
		}
		return dao.getSqlPageMapList(sql.toString(), params, page);
	}
	
	@Override
	public Pagination getDelCompByLinxuan(Pagination page){
		StringBuffer sql = new StringBuffer()
		.append("SELECT DISTINCT a.del_comp_code compCode,b.comp_name compName, ")
		.append(" b.org_code orgCode,c.contact_name contactName,c.contact_telephone contactTelephone,")
		.append("(select count(*) from base_delrelationship p where p.del_comp_code=a.del_comp_code and p.goods_code=:goodsCode) as areaCount ")
		.append(" FROM base_del_area_rel a ")
		.append(" LEFT JOIN std_compinfo b ON a.del_comp_code=b.comp_code ")
		.append(" LEFT JOIN std_compinfo_detail c ON b.comp_id=c.comp_id ")
		.append(" WHERE a.status=2 AND b.status=1 ") 
		.append(" AND a.area_code LIKE :province ");
		Map<String, Object> params = new HashMap<String, Object>();
		params.put("goodsCode", page.getConditions().get("goodsCode"));
		params.put("province", province.substring(0, 2)+"%");
		if (StringUtils.isNotBlank(page.getConditions().get("areaCode"))){
			sql.append(" and a.area_code like :areaCode ");
			params.put("areaCode", AreaUtil.getAreaPrefix(page.getConditions().get("areaCode"))+"%");
		}
		if (StringUtils.isNotBlank(page.getConditions().get("compCode"))){
			sql.append(" and a.del_comp_code=:compCode");
			params.put("compCode", page.getConditions().get("compCode").trim());
		}
		if (StringUtils.isNotBlank(page.getConditions().get("compName"))){
			sql.append(" and (b.comp_name like :compName or b.comp_spel_code like :compSpelCode)");
			params.put("compName", "%"+page.getConditions().get("compName").trim()+"%");
			params.put("compSpelCode", "%"+page.getConditions().get("compName").trim().toUpperCase()+"%");
		}
		return dao.getSqlPageMapList(sql.toString(), params, page);
	}
	public Pagination getDelArea(Pagination page){
		StringBuffer sql = new StringBuffer()
		.append("select a.area_id areaCode,a.area_name areaName,b.area_id fatherCode,b.area_name fatherName")
		.append("  from dic_area a left join dic_area b on a.father_id=b.area_id")
		.append("  where a.area_id not in ")
		.append("  (select c.area_code from base_delrelationship c where ")
		.append("   c.goods_code=:goodsCode and c.del_comp_code=:compCode)")
		.append(" and a.status=1 and a.area_id like :goodsArea and a.area_id not like '%00'");
		Map<String, Object> params = new HashMap<String, Object>();
		Map<String, String> map = page.getConditions();
		params.put("goodsCode", map.get("goodsCode"));
		params.put("goodsArea", AreaUtil.getAreaPrefix(map.get("goodsArea"))+"%");
		params.put("compCode", map.get("compCode"));
		if (StringUtils.isNotBlank(map.get("areaCode"))) {
			sql.append(" and a.area_id=:areaId");
			params.put("areaId", map.get("areaCode"));
		}
		if (StringUtils.isNotBlank(map.get("fatherCode"))) {
			sql.append(" and a.father_id = :fatherCode");
			params.put("fatherCode", map.get("fatherCode"));
		}
		sql.append(" order by a.area_id");
		page = dao.getSqlPageMapList(sql.toString(), params, page);
		return page;
	}
	
	@Override
	public Pagination getDelAreaByLinxuan(Pagination page){
		StringBuffer sql = new StringBuffer()
		.append("select a.area_id as areaCode,a.area_name as areaName,b.area_id fatherCode,b.area_name fatherName,t.del_range delRange")
		.append("  from base_del_area_rel t left join dic_area a on a.area_id = t.area_code left join dic_area b on a.father_id=b.area_id")
		.append("  where not EXISTS")
		.append("  (select * from base_delrelationship c where c.area_code=t.area_code ")
		.append(" and c.goods_code=:goodsCode and c.del_comp_code=:compCode)")
		.append(" and t.status = 2 and a.status=1 and a.area_id like :goodsArea and a.area_id not like '%00' and t.del_comp_code=:compCode ");
		Map<String, Object> params = new HashMap<String, Object>();
		Map<String, String> map = page.getConditions();
		params.put("goodsCode", map.get("goodsCode"));
		params.put("goodsArea", AreaUtil.getAreaPrefix(map.get("goodsArea"))+"%");
		params.put("compCode", map.get("compCode"));
		if (StringUtils.isNotBlank(map.get("areaCode"))) {
			sql.append(" and a.area_id=:areaId");
			params.put("areaId", map.get("areaCode"));
		}
		if (StringUtils.isNotBlank(map.get("fatherCode"))) {
			sql.append(" and a.father_id = :fatherCode");
			params.put("fatherCode", map.get("fatherCode"));
		}
		sql.append(" order by a.area_id");
		page = dao.getSqlPageMapList(sql.toString(), params, page);
		return page;
	}
	
	public Pagination getSetDelArea(Pagination page){
		StringBuffer sql = new StringBuffer()
		.append("select a.del_rel_id delRelId,a.area_code areaCode,b.area_name areaName,c.area_id fatherAreaCode,c.area_name fatherAreaName,a.status ")
		.append(" from base_delrelationship a ")
		.append(" left join dic_area b on a.area_code=b.area_id")
		.append(" left join dic_area c on c.area_id=b.father_id")
		.append(" where a.goods_code=:goodsCode ");
		Map<String, Object> params = new HashMap<String, Object>();
		Map<String, String> map = page.getConditions();
		params.put("goodsCode", map.get("goodsCode"));
		if (StringUtils.isNotBlank(map.get("compCode"))) {
			sql.append("  and a.del_comp_code=:compCode");
			params.put("compCode", map.get("compCode"));
		}
		
		if (StringUtils.isNotBlank(map.get("fatherCode"))) {
			sql.append(" and b.father_id = :fatherCode");
			params.put("fatherCode", map.get("fatherCode"));
		}
		if (StringUtils.isNotBlank(map.get("areaCode"))) {
			sql.append(" and a.area_code = :areaCode");
			params.put("areaCode", map.get("areaCode"));
		}
		if (StringUtils.isNotBlank(map.get("status"))) {
			sql.append(" and a.status = :status");
			params.put("status", map.get("status"));
		}
		return dao.getSqlPageMapList(sql.toString(), params, page);
	}
	public Pagination getSetDelAreaLog(Pagination page){
		StringBuffer sql = new StringBuffer()
		.append("SELECT a.del_rel_id delRelId,a.area_code areaCode,b.area_name areaName,c.area_id fatherAreaCode,c.area_name fatherAreaName,a.status,a.upd_datetime updDatetime")
		.append("  from base_delrelationship_log a ")
		.append(" left join dic_area b on a.area_code=b.area_id")
		.append(" left join dic_area c on c.area_id=b.father_id")
		.append(" where a.goods_code=:goodsCode and a.del_comp_code=:compCode");
		Map<String, Object> params = new HashMap<String, Object>();
		Map<String, String> map = page.getConditions();
		params.put("goodsCode", map.get("goodsCode"));
		params.put("compCode", map.get("compCode"));
		if (StringUtils.isNotBlank(map.get("fatherCode"))) {
			sql.append(" and b.father_id = :fatherCode");
			params.put("fatherCode", map.get("fatherCode"));
		}
		if (StringUtils.isNotBlank(map.get("areaCode"))) {
			sql.append(" and a.area_code = :areaCode");
			params.put("areaCode", map.get("areaCode"));
		}
		if (StringUtils.isNotBlank(page.getConditions().get("startDate"))){
			sql.append(" and a.upd_datetime >=:startDate");
			params.put("startDate", page.getConditions().get("startDate").trim()+" 00:00:00");
		}
		if (StringUtils.isNotBlank(page.getConditions().get("endDate"))){
			sql.append(" and a.upd_datetime <=:endDate");
			params.put("endDate", page.getConditions().get("endDate").trim()+" 23:59:59");
		}
		sql.append(" order by a.upd_datetime desc");
		return dao.getSqlPageMapList(sql.toString(), params, page);
	}
	/**
	 * 
	 *@category 写配送关系日志
	 *@author 邱磊
	 *@date 2015年6月17日 下午9:05:25
	 *@param data
	 */
	@SuppressWarnings("unused")
	private void writeLog(BaseDelrelation data) {
		if (null != data) {
			BaseDelrelationLog log = new BaseDelrelationLog();
			BeanUtils.copyProperties(data, log);
			log.setAreaCode(data.getDicArea().getAreaid());
			log.setProdCompId(data.getStdCompinfo()==null?"":data.getStdCompinfo().getCompId());
			log.setDelCompId(data.getStdPSCompinfo()==null?"":data.getStdPSCompinfo().getCompId());
			log.setLogId(UUID.randomUUID().toString());
			log.setUpdDatetime(new Timestamp(System.currentTimeMillis()));
			log.setUpdUser(data.getUpdUser());
			dao.add(log);
		}
	}
	/**
	 * 
	 *@category 根据商品码，配送企业，配送区域查看有没有记录
	 *@author 邱磊
	 *@date 2015年6月17日 下午9:05:42
	 *@param drugId
	 *@param prodCompId
	 *@param areaId
	 *@return
	 */
	@SuppressWarnings({ "unchecked", "unused" })
	private List<Object[]> getBaseDelrelationListBy(String drugId,
			String delCompId, String areaId) {
		String sql = "SELECT * from base_delrelation a where a.area_code=:areaCode and a.goods_code=:drugId and a.del_comp_id=:compId ";
		Map<String, Object> params = new HashMap<String, Object>();
		params.put("areaCode", areaId);
		params.put("drugId", drugId);
		params.put("compId", delCompId);
		List<Object[]> list = (List<Object[]>) dao.getSqlList(sql, params);
		return list;
	}
	
	public int getAreaDelCount(String areaCode,String goodsCode){
		StringBuffer sql = new StringBuffer()
		.append("select count(b.del_comp_id) from base_delrelation b where b.`status`=1")
		.append(" and b.goods_code=:goodsCode and b.area_code=:areaCode");
		Map<String, Object> params = new HashMap<String, Object>();
		params.put("goodsCode", goodsCode);
		params.put("areaCode", areaCode);
	  BigInteger records = (BigInteger)dao.getSqlUniqueResult(sql.toString(), params);
		return records.intValue();
	}
	public int getAreaMaxDelCount(){
		String sql = "select a.config_value from sys_purconfig a where a.config_id=:configId";
		Map<String, Object> params = new HashMap<String, Object>();
		params.put("configId", Constants.CONFIG_ID);
		String num = (String) dao.getSqlUniqueResult(sql.toString(), params);
		int count = Integer.valueOf(num);
		return count;
	}	
	/**
	 * 
	 *@category 获取预览列表
	 *@author 邱磊
	 *@date 2015年11月12日 上午8:32:59
	 *@param
	 */
	public Pagination getImportGoodsView(Pagination page){
		StringBuffer sql = new StringBuffer()
		.append("select t.goods_code as goodsCode,t.proj_name as projName,t.drug_code as drugCode,a.qualityspec_name as qualityspecName,")
		.append("a.form_name as formName,a.spec_name as specName,b.factor,b.unit_name as unitName,")
		.append("b.material_name as materialName,b.comp_name as compName,t.pro_price_limit as proPriceLimit,t.retail_price_limit as retailPriceLimit,")
		.append("case t.is_longtime_plan when null then '否' else '是' end as isback,")
		.append("case t.is_longtime_plan when 1 then '长期备案' else '临时备案' end as isLongtimePlan")
		.append(" from base_goods_import t")
		.append(" left join std_drugqualityspec a on t.qualityspec_id=a.qualityspec_id")
		.append(" left join std_proddruginfo b on b.drug_code=t.drug_code")
		.append(" where t.file_id=:fileId");
		Map<String, Object> params = new HashMap<String, Object>();
		params.put("fileId", page.getConditions().get("fileId"));
		List<?> list = dao.getSqlMapList(sql.toString(), params);
		page.setRows(list);
		return page;
	}
	public int importGoods(String fileId){
		Session session = sessionFactory.openSession();
		StringBuffer sql = new StringBuffer()
		.append("insert into base_goods_item ")
		.append(" select a.goods_code,a.area_id,a.area_name,a.ar_shortname,a.ar_fullname,a.proj_id,a.proj_name,a.proj_starttime,a.proj_endtime,")
		.append(" a.proj_status,a.qualityspec_id,a.drug_code,a.qualityspec_name,a.qualityspec_name_spel,a.qualityspec_name_eng,a.form_name,")
		.append(" a.spec_name,a.qualityspec_status,a.bus_name,a.factor,a.unit_name,a.material_name,a.prod_compcode,a.prod_compname,")
		.append(" a.prod_compname_spel,a.prod_compstatus,a.cat_comp_code,a.cat_comp_name,a.cat_comp_name_spel,a.cat_comp_status,a.use_range,")
		.append(" a.drug_attr,a.goods_source,a.pattern_type,a.is_healthcare,a.is_agriculture,a.is_lowprice,a.is_smallprice,a.is_smallsource,")
		.append(" a.is_longtime_plan,a.drug_status,a.drug_cat,a.pro_price_limit,a.retail_price_limit,a.hang_price,a.ref_price,a.healthcare_price,")
		.append(" a.pic_url,a.pic_short_url,1,a.upd_user,a.upd_datetime from base_goods_import a")
		.append(" where a.file_id=:fileId")
		.append(" and not EXISTS")
		.append(" (select * from base_goods_item b where a.proj_id=b.proj_id and a.drug_code=b.drug_code)");
		Query query = session.createSQLQuery(sql.toString());
		query.setParameter("fileId", fileId);
		int flag = query.executeUpdate();
		session.clear();
		session.close();
		
		Map<String, Object> params = new HashMap<String, Object>();
		params.put("fileId", fileId);
		dao.executeSql(sql.toString(), params);
		StringBuffer logsql = new StringBuffer()
		.append("insert into base_goods_item_log ")
		.append(" select UUID(),a.goods_code,a.area_id,a.area_name,a.ar_shortname,a.ar_fullname,a.proj_id,a.proj_name,a.proj_starttime,a.proj_endtime,")
		.append(" a.proj_status,a.qualityspec_id,a.drug_code,a.qualityspec_name,a.qualityspec_name_spel,a.qualityspec_name_eng,a.form_name,")
		.append(" a.spec_name,a.qualityspec_status,a.bus_name,a.factor,a.unit_name,a.material_name,a.prod_compcode,a.prod_compname,")
		.append(" a.prod_compname_spel,a.prod_compstatus,a.cat_comp_code,a.cat_comp_name,a.cat_comp_name_spel,a.cat_comp_status,a.use_range,")
		.append(" a.drug_attr,a.goods_source,a.pattern_type,a.is_healthcare,a.is_agriculture,a.is_lowprice,a.is_smallprice,a.is_smallsource,")
		.append(" a.is_longtime_plan,a.drug_status,a.drug_cat,a.pro_price_limit,a.retail_price_limit,a.hang_price,a.ref_price,a.healthcare_price,")
		.append(" a.pic_url,a.pic_short_url,1,a.upd_user,a.upd_datetime,null from base_goods_import a")
		.append(" where a.file_id=:fileId")
		.append(" and not EXISTS")
		.append(" (select * from base_goods_item b where a.proj_id=b.proj_id and a.drug_code=b.drug_code)");
		dao.executeSql(logsql.toString(), params);
		sfmanager.updateImportFileStatus(Constants.WENJIAN_STATUS_DAORUCHENGGONG, fileId);
		String dsql = "DELETE from base_goods_import where file_id=:fileId";
		dao.executeSql(dsql, params);
		return flag;
	}
	@SuppressWarnings("unchecked")
	public List<Map<String, Object>> checkReImport(Pagination page){
		StringBuffer sql = new StringBuffer()
		.append("select count(*) as importCount,")
		.append(" count((select count(*)  from base_goods_item b where a.proj_id=b.proj_id and a.drug_code=b.drug_code)) as drugCount ")
		.append(" from base_goods_import a")
		.append(" where a.file_id = :fileId");
		Map<String, Object> params = new HashMap<String, Object>();
		params.put("fileId", page.getConditions().get("fileId"));
		return (List<Map<String, Object>>) dao.getSqlMapList(sql.toString(), params);
	}
	@SuppressWarnings("unchecked")
	public Map<String, Object> getGoodsByCode(String goodsCode){
		String sql = "select * from base_goods_item t where t.goods_code = :goodsCode";
		Map<String, Object> params = new HashMap<String, Object>();
		params.put("goodsCode", goodsCode);
		List<Map<String, Object>> list = (List<Map<String, Object>>) dao.getSqlMapList(sql, params);
		if (null != list && list.size() > 0){
			return list.get(0);
		}
		return null;
	}
	
	
	@Override
	public Pagination getProjDrugList(Pagination page) {
		String delCompName = page.getConditions().get("delCompName");
		String hospName = page.getConditions().get("hospName");
		Map<String, String> map = page.getConditions();
		StringBuffer sql = new StringBuffer()
		.append(" select del_rel_id delRelId,prod_comp_code prodCompCode,prod_comp_name prodCompName,del_comp_code delCompCode,del_comp_name delCompName,hosp_code hospCode,hosp_name hospName,status  FROM  base_delrelationship WHERE prod_comp_code=:compCode AND prod_comp_status=1 AND del_comp_status=1 AND hosp_status=1");
		
		if (StringUtils.isNotBlank(delCompName)) {
			sql.append(" and (del_comp_name like :delCompName or del_comp_name_spel like :delCompNameSpel ) ");
			map.put("delCompName", "%"+delCompName+"%");
			map.put("delCompNameSpel", "%"+delCompName.toUpperCase()+"%");
		}
		if (StringUtils.isNotBlank(hospName)) {
			sql.append(" and (hosp_name like :hospName or hosp_spel_code like :hospNameSpel ) ");
			map.put("hospName", "%"+hospName+"%");
			map.put("hospNameSpel", "%"+hospName.toUpperCase()+"%");
		}
		sql.append(" order by upd_datetime desc ");
		return dao.getPageMapList(sql.toString(), map, page);
	
	}
	
	@SuppressWarnings("unchecked")
	@Override
	public boolean isDelrelation(Pagination page) {
		 boolean  temp = false;
		 List<String> list =  getSession().createSQLQuery(" select proj_drug_id from bid_projdrugs where comp_code=:compCode AND status=1 AND proj_status=1 AND SYSDATE() BETWEEN proj_starttime AND proj_endtime ").setParameter("compCode", page.getConditions().get("compCode")).list();
		if(null!=list && list.size()>0){
			temp=true;
		}
		return temp;
	}
	
	/**
	 * 
	 *@category 获取配送企业列表
	 *@author wangbing
	 *@date 2015年11月12日 上午8:32:59
	 *@param
	 */
	@Override
	public Pagination getTheDelCompList(Pagination page){
		String compCode = page.getConditions().get("compCode");
		String compName = page.getConditions().get("compName");
		StringBuffer sql = new StringBuffer()
		.append(" select distinct b.comp_code,b.comp_name,b.org_code,c.contact_name,c.contact_telephone,")
		.append(" (select count(*) from base_delrelationship t where t.del_comp_code = a.comp_code and t.hosp_code is not null)hospCount")
		.append(" from bid_projcompany a left join std_compinfo b on b.comp_code = a.comp_code ")
		.append(" left join std_compinfo_detail c on c.comp_id = b.comp_id")
		.append(" where a.status = 1 ");
		Map<String, Object> params = new HashMap<String, Object>();
		if(StringUtils.isNotBlank(compCode)){
			sql.append(" and b.comp_code = :compCode");
			params.put("compCode", compCode.trim());
		}
		if(StringUtils.isNotBlank(compName)){
			sql.append(" and (b.comp_name like :compName or b.comp_spel_code like :compNameSpel)");
			params.put("compName", "%" + compName.trim() + "%");
			params.put("compNameSpel", "%" + compName.trim().toUpperCase() + "%");
		}
		return dao.getSqlPageMapList(sql.toString(), params, page);
	}
	
	/**
	 * 
	 *@category 获取配送企业列表(遴选的)
	 *@author wangbing
	 *@date 2015年11月12日 上午8:32:59
	 *@param
	 */
	@Override
	public Pagination getTheDelCompListByLinxuan(Pagination page){
		String compCode = page.getConditions().get("compCode");
		String compName = page.getConditions().get("compName");
		StringBuffer sql = new StringBuffer()
		.append(" select distinct b.comp_code,b.comp_name,b.org_code,c.contact_name,c.contact_telephone,")
		.append(" (select count(*) from base_delrelationship t where t.del_comp_code = a.del_comp_code and t.hosp_code is not null)hospCount")
		.append(" from base_del_area_rel a left join std_compinfo b on b.comp_code = a.del_comp_code ")
		.append(" left join std_compinfo_detail c on c.comp_id = b.comp_id")
		.append(" where a.status = 2 ");
		Map<String, Object> params = new HashMap<String, Object>();
		if(StringUtils.isNotBlank(compCode)){
			sql.append(" and b.comp_code = :compCode");
			params.put("compCode", compCode.trim());
		}
		if(StringUtils.isNotBlank(compName)){
			sql.append(" and (b.comp_name like :compName or b.comp_spel_code like :compNameSpel)");
			params.put("compName", "%" + compName.trim() + "%");
			params.put("compNameSpel", "%" + compName.trim().toUpperCase() + "%");
		}
		return dao.getSqlPageMapList(sql.toString(), params, page);
	}
	
	/**
	 * 
	 *@category 获取已设置医疗机构列表
	 *@author wangbing
	 *@date 2015年11月12日 上午8:32:59
	 *@param
	 */
	@Override
	public Pagination getDelRelationHospList(Pagination page){
		String delCompCode = page.getConditions().get("delCompCode");
		String prodCompCode = page.getConditions().get("prodCompCode");
		String hospCode = page.getConditions().get("hospCode");
		String hospName = page.getConditions().get("hospName");
		String areaCode = page.getConditions().get("areaCode");
		StringBuffer sql = new StringBuffer()
		.append("select * from base_delrelationship where area_code like :areaCode and prod_comp_code = :prodCompCode and del_comp_code = :delCompCode ");
		Map<String, Object> params = new HashMap<String, Object>();
		params.put("areaCode", areaCode + "%");
		params.put("delCompCode", delCompCode);
		params.put("prodCompCode", prodCompCode);
		if(StringUtils.isNotBlank(hospCode)){
			sql.append(" and hosp_code = :hospCode");
			params.put("hospCode", hospCode);
		}
		if(StringUtils.isNotBlank(hospName)){
			sql.append(" and (hosp_name like :hospName or hosp_spel_code like :hospNameSpel)");
			params.put("hospName", "%" + hospName.trim() + "%");
			params.put("hospNameSpel", "%" + hospName.trim().toUpperCase() + "%");
		}
		return dao.getPageMapList(sql.toString(), params, page);
	}
	
	/**
	 * 
	 *@category 获取已设置医疗机构日志
	 *@author wangbing
	 *@date 2015年11月12日 上午8:32:59
	 *@param
	 */
	@Override
	public Pagination getDelRelationHospLog(Pagination page){
		String delCompCode = page.getConditions().get("delCompCode");
		String prodCompCode = page.getConditions().get("prodCompCode");
		String hospCode = page.getConditions().get("hospCode");
		String hospName = page.getConditions().get("hospName");
		String areaCode = page.getConditions().get("areaCode");
		StringBuffer sql = new StringBuffer()
		.append("select * from base_delrelationship_log where area_code like :areaCode and prod_comp_code = :prodCompCode and del_comp_code = :delCompCode ");
		Map<String, Object> params = new HashMap<String, Object>();
		params.put("areaCode", areaCode + "%");
		params.put("delCompCode", delCompCode);
		params.put("prodCompCode", prodCompCode);
		if(StringUtils.isNotBlank(hospCode)){
			sql.append(" and hosp_code = :hospCode");
			params.put("hospCode", hospCode);
		}
		if(StringUtils.isNotBlank(hospName)){
			sql.append(" and (hosp_name like :hospName or hosp_spel_code like :hospNameSpel)");
			params.put("hospName", "%" + hospName.trim() + "%");
			params.put("hospNameSpel", "%" + hospName.trim().toUpperCase() + "%");
		}
		return dao.getPageMapList(sql.toString(), params, page);
	}
	
	/**
	 * 
	 *@category 获取未设置配送关系医疗机构列表
	 *@author wangbing
	 *@date 2015年11月12日 上午8:32:59
	 *@param
	 */
	@Override
	public Pagination getNoSetDelRelHospList(Pagination page){
		String hospCode = page.getConditions().get("hospCode");
		String hospName = page.getConditions().get("hospName");
		String areaCode = page.getConditions().get("areaCode");
		StringBuffer sql = new StringBuffer()
		.append(" select distinct b.hosp_code,b.hosp_name,c.ar_fullname,b.hosp_classify ")
		.append(" from bid_projhospital a left join std_hospinfo b on b.hosp_code = a.hosp_code ")
		.append(" left join dic_area c on c.area_id = b.area_id ")
		.append(" where a.status = 1 and b.area_id like :areaCode and a.hosp_code not in (select hosp_code from base_delrelationship)");
		Map<String, Object> params = new HashMap<String, Object>();
		params.put("areaCode", areaCode + "%");
		if(StringUtils.isNotBlank(hospCode)){
			sql.append(" and b.hosp_code = :hospCode");
			params.put("hospCode", hospCode);
		}
		if(StringUtils.isNotBlank(hospName)){
			sql.append(" and (b.hosp_name like :hospName or b.hosp_spel_code like :hospNameSpel)");
			params.put("hospName", "%" + hospName.trim() + "%");
			params.put("hospNameSpel", "%" + hospName.trim().toUpperCase() + "%");
		}
		sql.append(" order by b.hosp_code");
		return dao.getSqlPageMapList(sql.toString(), params, page);
	}
	
	/**
	 * 
	 *@category 获取未设置配送关系医疗机构列表(遴选)
	 *@author wangbing
	 *@date 2015年11月12日 上午8:32:59
	 *@param
	 */
	@Override
	public Pagination getNoSetDelRelHospListByLinxuan(Pagination page){
		String delCompCode = page.getConditions().get("delCompCode");
		String hospCode = page.getConditions().get("hospCode");
		String hospName = page.getConditions().get("hospName");
		String areaCode = page.getConditions().get("areaCode");
		StringBuffer sql = new StringBuffer()
		.append(" select distinct b.hosp_code,b.hosp_name,c.ar_fullname,b.hosp_classify ")
		.append(" from bid_projhospital a left join std_hospinfo b on b.hosp_code = a.hosp_code ")
		.append(" left join dic_area c on c.area_id = b.area_id ")
		.append(" where a.status = 1 and b.area_id like :areaCode and a.hosp_code not in (select hosp_code from base_delrelationship)")
		.append(" and b.area_id in (select f.area_code from base_del_area_rel f where f.del_comp_code = :delCompCode and f.status = 2  ")
		.append(" and (f.del_range = 0 or f.del_range = b.hosp_classify)) ");
		Map<String, Object> params = new HashMap<String, Object>();
		params.put("areaCode", areaCode + "%");
		params.put("delCompCode", delCompCode);
		if(StringUtils.isNotBlank(hospCode)){
			sql.append(" and b.hosp_code = :hospCode");
			params.put("hospCode", hospCode);
		}
		if(StringUtils.isNotBlank(hospName)){
			sql.append(" and (b.hosp_name like :hospName or b.hosp_spel_code like :hospNameSpel)");
			params.put("hospName", "%" + hospName.trim() + "%");
			params.put("hospNameSpel", "%" + hospName.trim().toUpperCase() + "%");
		}
		sql.append(" order by b.hosp_code");
		return dao.getSqlPageMapList(sql.toString(), params, page);
	}
	
	/**
	 * 
	 *@category 根据配送企业设置医疗机构
	 *@author wangbing
	 *@date 2015年6月17日 下午4:58:22
	 *@return
	 */
	@Override
	public int setHospByDelComp(List<Map<String,String>> list,String updUser){
		int sum = 0;
		for(Map<String,String> data : list){
			int count = setOneHospByDelComp(data,updUser);
			sum += count;
		}
		return sum;
	}
	
	/**
	 * 
	 *@category 根据配送企业设置一个医疗机构
	 *@author wangbing
	 *@date 2015年6月17日 下午4:58:22
	 *@return
	 */
	private int setOneHospByDelComp(Map<String,String> data,String updUser){
		String prodCompCode = data.get("prodCompCode");
		String delCompCode = data.get("delCompCode");
		String hospCode = data.get("hospCode");
		String uuid = UUID.randomUUID().toString();
		Session session = sessionFactory.getCurrentSession();
		StringBuffer sql = new StringBuffer()
		.append("insert into base_delrelationship select :uuid,null goods_code,a.comp_code,a.comp_name,")
		.append("a.comp_spel_code,a.status,c.area_id,c.area_name,c.ar_shortname,c.ar_fullname,d.comp_code,")
		.append("d.comp_name,d.comp_spel_code,d.status,b.hosp_code,b.hosp_name,b.hosp_spel_code,b.status,")
		.append("b.hosp_classify del_range,0 status,:updUser upd_user,sysdate() upd_datetime from std_compinfo a, ")
		.append("std_compinfo d,std_hospinfo b , dic_area c  ")
		.append("where a.comp_code = :prodCompCode and b.hosp_code = :hospCode and d.comp_code = :delCompCode and c.area_id = b.area_id  ");
		Query query = session.createSQLQuery(sql.toString());
		query.setString("uuid", uuid);
		query.setString("updUser", updUser);
		query.setString("prodCompCode", prodCompCode);
		query.setString("hospCode", hospCode);
		query.setString("delCompCode", delCompCode);
		int count = query.executeUpdate();
		session.flush();
		writeDelLog(uuid);
		return count;
	}
	
	/**
	 * 
	 *@category 写配送关系日志
	 *@author wangbing
	 *@date 2015年6月17日 下午4:58:22
	 *@return
	 */
	private void writeDelLog(String id){
		Session session = sessionFactory.getCurrentSession();
		StringBuffer sql = new StringBuffer()
		.append("insert into base_delrelationship_log select :uuid,t.* from base_delrelationship t where t.del_rel_id = :id ");
		Query query = session.createSQLQuery(sql.toString());
		query.setString("uuid", UUID.randomUUID().toString());
		query.setString("id", id);
		query.executeUpdate();
		session.flush();
	}
		@Override
	public Pagination getHospList(Pagination page) {
		String hospName =page.getConditions().get("hospName");
		StringBuffer sb =new StringBuffer()
		.append(" select distinct t.hosp_code hospCode,b.hosp_name hospName,b.org_code orgCode,c.contact_name contactName,c.contact_telephone contactTelephone, ")
		.append(" (select count(*) from base_delrelationship where prod_comp_code=:compCode and  hosp_code=t.hosp_code ) compCount ")
		.append("  from bid_projhospital t")
		.append(" join std_hospinfo b on t.hosp_code=b.hosp_code and b.status=1 ")
		.append(" left join std_hospinfo_detail c on b.hosp_id=c.hosp_id ")
		.append(" where t.status=1 and t.proj_status=1 and SYSDATE() BETWEEN t.proj_starttime and t.proj_endtime ");
		if(StringUtils.isNotBlank(hospName)){
			sb.append(" and (b.hosp_name like :hospName or b.hosp_spel_code like :hospNameSpel ) ");
			page.getConditions().put("hospName", "%"+hospName+"%");
			page.getConditions().put("hospNameSpel", "%"+hospName.toUpperCase()+"%");
		}
		return dao.getSqlPageMapList(sb.toString(), page.getConditions(), page);
	}
	
	
	
	@Override
	public Pagination getDelCompList(Pagination page) {
		String delCompName =page.getConditions().get("delCompName");
		String delCompCode =page.getConditions().get("delCompCode");
		
		StringBuffer sb =new StringBuffer()
		.append(" SELECT distinct a.comp_code compCode,")
		.append(" 	b.comp_name compName,")
		.append(" 	b.org_code orgCode,")
		.append(" 	c.contact_telephone contactPhone,")
		.append(" 	c.contact_name contactName,")
		.append(" 	(")
		.append(" 		SELECT")
		.append(" 			count(*)")
		.append(" 		FROM")
		.append(" 			base_delrelationship g")
		.append(" 		WHERE")
		.append(" 		 g.prod_comp_code = :compCode")
		.append(" 		AND a.comp_code=g.del_comp_code")
		.append(" 	) AS hospCount")
		.append(" FROM")
		.append("  bid_projcompany a ")
		.append(" JOIN std_compinfo b ON a.comp_code = b.comp_code")
		.append(" AND b. STATUS = 1")
		.append(" LEFT JOIN std_compinfo_detail c ON b.comp_id = c.comp_id")
		.append(" WHERE")
		.append(" 	a. STATUS = 1")
		.append(" AND a.proj_status = 1")
		.append(" AND SYSDATE() BETWEEN a.proj_starttime")
		.append(" AND a.proj_endtime");
		if(StringUtils.isNotBlank(delCompName)){
			sb.append(" and (b.comp_name like :delCompName or b.comp_spel_code like :delCompNameSpel ) ");
			page.getConditions().put("delCompName", "%"+delCompName+"%");
			page.getConditions().put("delCompNameSpel", "%"+delCompName.toUpperCase()+"%");
		}
		if(StringUtils.isNotBlank(delCompCode)){
			sb.append(" and b.comp_code =:delCompCode ");
		}
		return dao.getSqlPageMapList(sb.toString(), page.getConditions(), page);
	
	}
	@Override
	public Pagination getHospJspList(Pagination page) {
		String hospName =page.getConditions().get("hospName");
		String hospCode =page.getConditions().get("hospCode");
		String status =page.getConditions().get("status");
		StringBuffer sb =new StringBuffer()
		.append(" select a.del_rel_id delRelId, a.hosp_code hospCode,a.hosp_name hospName,b.org_code orgCode,c.contact_name contactName,c.contact_telephone contactTelephone,a.status")
		.append("  from base_delrelationship a ")
		.append(" join std_hospinfo b on a.hosp_code=b.hosp_code and b.status=1 ")
		.append(" left join std_hospinfo_detail c on b.hosp_id=c.hosp_id ")
		.append(" where a.prod_comp_code=:compCode and a.del_comp_code=:delCompCode ");
		if(StringUtils.isNotBlank(hospName)){
			sb.append(" and (b.hosp_name like :hospName or b.hosp_spel_code like :hospName ) ");
			page.getConditions().put("hospName", "%"+hospName+"%");
		}
		if(StringUtils.isNotBlank(hospCode)){
			sb.append(" and a.hosp_code =:hospCode  ");
		}
		if(StringUtils.isNotBlank(status)){
			sb.append(" and a.status =:status ");
		}
		sb.append(" order by  a.upd_datetime desc ");
		return dao.getSqlPageMapList(sb.toString(), page.getConditions(), page);
		
	}
	@Override
	public Pagination getCompJspList(Pagination page) {
		String delCompName =page.getConditions().get("delCompName");
		String delCompCode =page.getConditions().get("delCompCode");
		String status =page.getConditions().get("status");
		StringBuffer sb =new StringBuffer()
		.append(" select a.del_rel_id delRelId, a.del_comp_code compCode,a.del_comp_name compName,b.org_code orgCode,c.contact_telephone contactPhone,c.contact_name contactName,a.status ")
		.append("  from base_delrelationship a ")
		.append("  join std_compinfo b on a.del_comp_code=b.comp_code and b.status=1 ")
		.append("  left join std_compinfo_detail c on b.comp_id=c.comp_id ")
		.append("  where a.prod_comp_code=:compCode and a.hosp_code=:hospCode ");
		if(StringUtils.isNotBlank(delCompName)){
			sb.append(" and (a.del_comp_name like :delCompName or a.del_comp_name_spel like :delCompName ) ");
			page.getConditions().put("delCompName", "%"+delCompName+"%");
		}
		if(StringUtils.isNotBlank(delCompCode)){
			sb.append(" and a.del_comp_code =:delCompCode  ");
		}
		if(StringUtils.isNotBlank(status)){
			sb.append(" and a.status =:status  ");
		}
		sb.append(" order by  a.upd_datetime desc ");
		return dao.getSqlPageMapList(sb.toString(), page.getConditions(), page);
	}
	@Override
	public Pagination getDelHospJspList(Pagination page) {
		String hospName =page.getConditions().get("hospName");
		String hospCode =page.getConditions().get("hospCode");
		StringBuffer sb =new StringBuffer()
		.append(" SELECT DISTINCT")
		.append(" 	t.hosp_code hospCode,")
		.append(" 	b.hosp_name hospName,")
		.append(" 	b.org_code orgCode,")
		.append(" 	c.contact_name contactName,")
		.append(" 	c.contact_telephone contactTelephone")
		.append(" FROM")
		.append("  bid_projhospital t JOIN std_hospinfo b ON t.hosp_code = b.hosp_code")
		.append(" AND b. STATUS = 1")
		.append(" LEFT JOIN std_hospinfo_detail c ON b.hosp_id = c.hosp_id")
		.append(" WHERE")
		.append(" 	t. STATUS = 1")
		.append(" AND t.proj_status = 1")
		.append(" AND SYSDATE() BETWEEN t.proj_starttime")
		.append(" AND t.proj_endtime")
		.append(" AND t.hosp_code NOT IN (")
		.append(" 	SELECT")
		.append(" 		g.hosp_code")
		.append(" 	FROM")
		.append(" 		base_delrelationship g")
		.append(" 	WHERE g.del_comp_code =:delCompCode AND g.prod_comp_code=:compCode  ")
		.append(" )");

		if(StringUtils.isNotBlank(hospName)){
			sb.append(" and (b.hosp_name like :hospName or b.hosp_spel_code like :hospName ) ");
			page.getConditions().put("hospName", "%"+hospName+"%");
		}
		if(StringUtils.isNotBlank(hospCode)){
			sb.append(" and t.hosp_code =:hospCode  ");
		}
		return dao.getSqlPageMapList(sb.toString(), page.getConditions(), page);
	}
	@Override
	public Pagination getDelCompJspList(Pagination page) {
		String delCompName =page.getConditions().get("delCompName");
		String delCompCode =page.getConditions().get("delCompCode");
		StringBuffer sb =new StringBuffer()
		.append(" select distinct a.comp_code compCode,b.comp_name compName,b.org_code orgCode,c.contact_telephone contactPhone,c.contact_name contactName ")
		.append("  from bid_projcompany a  ")
		.append("  join std_compinfo b on a.comp_code=b.comp_code and b.status=1 ")
		.append("  left join std_compinfo_detail c on b.comp_id=c.comp_id ")
		.append("  where a.status=1 and a.proj_status=1 and SYSDATE() BETWEEN a.proj_starttime and a.proj_endtime ")
		.append(" and a.comp_code not in ( select g.del_comp_code  from base_delrelationship g  ")
		.append(" where g.prod_comp_code=:compCode AND g.hosp_code=:hospCode ) ");
		if(StringUtils.isNotBlank(delCompName)){
			sb.append(" and (b.comp_name like :delCompName or b.comp_spel_code like :delCompName ) ");
			page.getConditions().put("delCompName", "%"+delCompName+"%");
		}
		if(StringUtils.isNotBlank(delCompCode)){
			sb.append(" and a.comp_code =:delCompCode  ");
		}
		return dao.getSqlPageMapList(sb.toString(), page.getConditions(), page);
	}
	
 	@Override
	public void setDelWithHosp(Pagination page) {
		String hospCodes =page.getConditions().get("hospCodes");
		String userId =page.getConditions().get("userId"); 
		String delCompCode =page.getConditions().get("delCompCode"); 
		StdCompinfo sc = (StdCompinfo) page.getForm();
		List<String> list = new ArrayList<String>();
		String[] strs = hospCodes.split(",");
		for(int i=0,temp=strs.length;i<temp;i++){
			list.add(strs[i]);
		}
		Date now =new Date();
		StringBuffer sb =new StringBuffer()
		.append(" insert into base_delrelationship (del_rel_id,prod_comp_code,prod_comp_name,prod_comp_name_spel,prod_comp_status,")
		.append(" del_comp_code,del_comp_name,del_comp_name_spel,del_comp_status,")
		.append(" hosp_code,hosp_name,hosp_spel_code,hosp_status,del_range,status,upd_user,upd_datetime)")
		.append(" SELECT uuid(),:compCode,:compName,:compNameSpel,:compStatus,")
		.append(" sc.comp_code,sc.comp_name,sc.comp_spel_code,sc.`status`,")
		.append(" sh.hosp_code,sh.hosp_name,sh.hosp_spel_code,sh.`status`,:delRange,:status,:updUser,:updDatetime")
		.append(" from std_compinfo sc ,std_hospinfo sh where sc.comp_code =:delCompCode  and sh.hosp_code in (:list)");
		Session session =getSession();
		SQLQuery query = session.createSQLQuery(sb.toString());
		query.setParameterList("list", list)
		.setParameter("compCode", sc.getCompCode())
		.setParameter("compName", sc.getCompName())
		.setParameter("compNameSpel", sc.getCompSpelCode())
		.setParameter("compStatus", sc.getStatus())
		.setParameter("delRange", Constants.DEL_RANGE_ALL)
		.setParameter("status", Constants.DEL_STATUS_UNCONFIRMED)
		.setParameter("updUser", userId)
		.setParameter("updDatetime",now)
		.setParameter("delCompCode",delCompCode)
		.executeUpdate();
		
		StringBuffer sbLog =new StringBuffer()
		.append(" insert into base_delrelationship_log select uuid(),t.* from base_delrelationship t where t.prod_comp_code=:compCode and t.hosp_code in (:list)  and t.del_comp_code =:delCompCode");
		SQLQuery queryLog = session.createSQLQuery(sbLog.toString());
		queryLog.setParameter("compCode", sc.getCompCode())
		.setParameter("delCompCode", delCompCode)
		.setParameterList("list", list)
		.executeUpdate();
		
		session.flush();
		session.clear();
	} 
	
	@Override
	public void setDelWithComp(Pagination page) {
		String userId =page.getConditions().get("userId"); 
		String hospCode =page.getConditions().get("hospCode"); 
		String delCompCodes =page.getConditions().get("delCompCodes"); 
		StdCompinfo sc = (StdCompinfo) page.getForm();
		List<String> list = new ArrayList<String>();
		String[] strs = delCompCodes.split(",");
		for(int i=0,temp=strs.length;i<temp;i++){
			list.add(strs[i]);
		}
		Date now =new Date();
		StringBuffer sb =new StringBuffer()
		.append(" insert into base_delrelationship (del_rel_id,prod_comp_code,prod_comp_name,prod_comp_name_spel,prod_comp_status,")
		.append(" del_comp_code,del_comp_name,del_comp_name_spel,del_comp_status,")
		.append(" hosp_code,hosp_name,hosp_spel_code,hosp_status,del_range,status,upd_user,upd_datetime)")
		
		.append(" SELECT uuid(),:compCode,:compName,:compNameSpel,:compStatus,")
		.append(" sc.comp_code,sc.comp_name,sc.comp_spel_code,sc.`status`,")
		.append(" sh.hosp_code,sh.hosp_name,sh.hosp_spel_code,sh.`status`,:delRange,:status,:updUser,:updDatetime")
		.append(" from std_compinfo sc ,std_hospinfo sh where sh.hosp_code =:hospCode and sc.comp_code in (:list)  ");
		Session session =getSession();
		SQLQuery query = session.createSQLQuery(sb.toString());
		query.setParameterList("list", list)
		.setParameter("compCode", sc.getCompCode())
		.setParameter("compName", sc.getCompName())
		.setParameter("compNameSpel", sc.getCompSpelCode())
		.setParameter("compStatus", sc.getStatus())
		.setParameter("delRange", Constants.DEL_RANGE_ALL)
		.setParameter("status", Constants.DEL_STATUS_UNCONFIRMED)
		.setParameter("updUser", userId)
		.setParameter("updDatetime",now)
		.setParameter("hospCode",hospCode)
		.executeUpdate();
		
		StringBuffer sbLog =new StringBuffer()
		.append(" insert into base_delrelationship_log select uuid(),t.* from base_delrelationship t where t.prod_comp_code=:compCode and t.hosp_code=:hospCode and t.del_comp_code in (:list) ");
		SQLQuery queryLog = session.createSQLQuery(sbLog.toString());
		queryLog.setParameter("compCode", sc.getCompCode())
		.setParameter("hospCode", hospCode)
		.setParameterList("list", list)
		.executeUpdate();
		
		session.flush();
		session.clear();
		
		
		
		
		
		
	}
	/* (non-Javadoc)
	 * @see com.hsnn.medstgmini.pro.base.dao.BaseGoodsDao#getDelCompListByLinxuan(com.hsnn.medstgmini.util.Pagination)
	 */
	@Override
	public Pagination getDelCompListByLinxuan(Pagination page) {
		String compCode = page.getConditions().get("compCode");
		String compName = page.getConditions().get("compName");
		StringBuffer sql = new StringBuffer()
		.append("	select distinct b.comp_code compCode,b.comp_name compName,b.org_code orgCode,c.contact_name contactName,c.contact_telephone contactPhone,")
		.append("	(select count(*) from base_delrelationship t where t.del_comp_code = a.del_comp_code and t.hosp_code is not null) hospCount")
		.append(" 	from base_del_area_rel a left join std_compinfo b on b.comp_code = a.del_comp_code ")
		.append("	left join std_compinfo_detail c on c.comp_id = b.comp_id")
		.append("	where a.status = 2 ");
		Map<String, Object> params = new HashMap<String, Object>();
		if(StringUtils.isNotBlank(compCode)){
			sql.append(" and b.comp_code = :compCode");
			params.put("compCode", compCode.trim());
		}
		if(StringUtils.isNotBlank(compName)){
			sql.append(" and (b.comp_name like :compName or b.comp_spel_code like :compName)");
			params.put("compName", "%"+compName.trim()+"%");
		}
		return dao.getSqlPageMapList(sql.toString(), params, page);
	
	}

	@SuppressWarnings("unchecked")
	@Override
	public Pagination getDelRelLog(Pagination page) {
		String delCompCode = page.getConditions().get("delCompCode");
		String delCompName = page.getConditions().get("delCompName");
		String hospCode = page.getConditions().get("hospCode");
		String hospName = page.getConditions().get("hospName");
		
		StringBuffer sql = new StringBuffer()
		.append("select * from base_delrelationship_log where  prod_comp_code = :compCode  ");
		Map<String, String> map =page.getConditions();
		if(StringUtils.isNotBlank(delCompCode)){
			sql.append("  and del_comp_code = :delCompCode ");
			map.put("delCompCode", delCompCode);
		}
		if(StringUtils.isNotBlank(delCompName)){
			sql.append("  and (del_comp_name like :delCompName or del_comp_name_spel like :delCompName) ");
			map.put("delCompName","%"+delCompName+"%" );
		}
		if(StringUtils.isNotBlank(hospCode)){
			sql.append(" and hosp_code = :hospCode");
			map.put("hospCode", hospCode);
		}
		if(StringUtils.isNotBlank(hospName)){
			sql.append(" and (hosp_name like :hospName or hosp_spel_code like :hospName)");
			map.put("hospName", "%"+hospName+"%");
		}
		sql.append(" order by upd_datetime desc ");
		page = dao.getPageMapList(sql.toString(), map, page);
		
		List<Map<String,Object>> list = (List<Map<String, Object>>)page.getRows();
		for(Map<String,Object> data : list){
			String delRange = data.get("del_range").toString();
			String status = data.get("status").toString();
			data.put("del_range", "0".equals(delRange)?"全部":("1".equals(delRange)?"县及县以上医疗机构":("2".equals(delRange)?"基层医疗机构":"其他")) );
			data.put("status",  "0".equals(status)?"未确认":("1".equals(status)?"已确认":("2".equals(status)?"拒绝":("3".equals(status)?"解除配送关系":"其他"))));
			data.put("upd_datetime", DateUtil.dateTime((Timestamp)data.get("upd_datetime")));
		}
		return page;
	}
	public Pagination getHaveSetDel(Pagination page){
		StringBuffer sql = new StringBuffer();
		if ("1".equals(page.getConditions().get("gzfjydel"))){
			sql.append("select distinct t.del_comp_code,t.del_comp_name,t.del_comp_name_short");
		}else {
			sql.append("select t.del_rel_id,t.del_comp_code,t.del_comp_name,t.del_comp_name_short");
		}
		sql.append(" from base_delrelationship t")
		.append(" where t.goods_code = :goodsCode");
		Map<String, Object> params = new HashMap<String, Object>();
		params.put("goodsCode", page.getConditions().get("goodsCode"));
		if (StringUtils.isNotBlank(page.getConditions().get("compName"))){
			sql.append(" and (t.del_comp_name like :compName or t.del_comp_name_spel like :compNameSpel)");
			params.put("compName", "%" + page.getConditions().get("compName").trim() +"%");
			params.put("compNameSpel", "%" + page.getConditions().get("compName").trim().toUpperCase() +"%");
		}
		if ("1".equals(page.getConditions().get("gzfjydel"))){
			sql.append(" and t.status = 1");
		}
		return dao.getSqlPageMapList(sql.toString(), params, page);
	}
	public void batchDeleteHaveSetDel(String goodsCode,List<String> list,String userId){
		String sql = "delete from base_delrelationship  where goods_code=:goodsCode and del_comp_code in (:compCode)";
		Map<String, Object> params = new HashMap<String, Object>();
		params.put("goodsCode", goodsCode);
		params.put("compCode", list);
		dao.executeSql(sql, params);
		StringBuffer hcatalog = new StringBuffer()
		.append("insert into pur_hospcatalog_log(log_id,hosp_pro_cat_id,hosp_code,goods_code,")
		.append("del_comp_code,pro_price,pro_count,status,bak_pur_count,")
		.append("upd_type,upd_datetime,upd_user) ")
		.append(" select UUID(),a.hosp_pro_cat_id,a.hosp_code,")
		.append(" a.goods_code,a.del_comp_code,a.pro_price,a.pro_count,a.status,a.bak_pur_count,")
		.append("1,:updDatetime,:updUser ")
		.append(" from pur_hospcatalog a")
		.append(" where a.goods_code=:goodsCode and a.del_comp_code in (:compCode)");
		params.put("updDatetime", new Date());
		params.put("updUser", userId);
		dao.executeSql(hcatalog.toString(), params);
		StringBuffer delCata = new StringBuffer()
		.append("DELETE from pur_hospcatalog where goods_code=:goodsCode and del_comp_code in (:compCode)");
		params.remove("updDatetime");
		params.remove("updUser");
		dao.executeSql(delCata.toString(), params);
		String delPlan = "delete from pur_purplan where goods_code=:goodsCode and del_comp_code in (:compCode)";
		dao.executeSql(delPlan, params);
	}
	
	public Pagination getGoodsSelectComp(Pagination page){
		
		if ("1".equals(enable)){
			String goodsCode = page.getConditions().get("goodsCode");
			StringBuffer sql = new StringBuffer();
			Map<String, Object> params = new HashMap<String, Object>();
			if("1".equals(page.getConditions().get("gzfjydel"))) {
				String areaId = page.getConditions().get("areaId");
				String areaPrefix = AreaUtil.getAreaPrefix(areaId);
				String[] superArea = AreaUtil.getSuperArea(areaId);
				
				sql.append("select DISTINCT a.del_comp_code,a.del_comp_name,a.del_comp_name_short from base_del_area_rel a where a.del_comp_code is not null and (a.area_code like :areaPrefix ");
				if(superArea != null){
					for(int i = 0;i<superArea.length;i++){
						sql.append(" or a.area_code = :superArea").append(i+"");
						params.put("superArea"+i, superArea[i]);
					}
				}
				sql.append(")");
				sql.append(" and a.del_comp_code not in ")
				.append(" (select c.del_comp_code from base_delrelationship c where c.goods_code=:goodsCode)");
				
				params.put("areaPrefix", "%"+areaPrefix+"%");
				params.put("goodsCode", goodsCode);
				if (StringUtils.isNotBlank(page.getConditions().get("compName"))){
					sql.append(" and (a.del_comp_name like :compName or a.del_comp_name_spel like :compNameSpel)");
					params.put("compName", "%" + page.getConditions().get("compName").trim() + "%");
					params.put("compNameSpel", "%" + page.getConditions().get("compName").trim().toUpperCase() + "%");
				}
			}else {
				Map<String, Object> map = this.getPorjInfoByGoodsCode(goodsCode);
				String areaCodes = projareaProp.getProperty(map.get("proj_code").toString());
				List<String> pl = Arrays.asList(areaCodes.split(","));
				
				sql.append("select DISTINCT a.del_comp_code,a.del_comp_name,a.del_comp_name_short from base_del_area_rel a where a.area_code in(")
				.append("select b.area_id from dic_area b")
				.append(" where b.status=1 and b.father_id in (:country))")
				.append(" and a.del_comp_code not in ")
				.append(" (select c.del_comp_code from base_delrelationship c where c.goods_code=:goodsCode)");
				
				params.put("country", pl);
				params.put("goodsCode", goodsCode);
				if (StringUtils.isNotBlank(page.getConditions().get("compName"))){
					sql.append(" and (a.del_comp_name like :compName or a.del_comp_name_spel like :compNameSpel)");
					params.put("compName", "%" + page.getConditions().get("compName").trim() + "%");
					params.put("compNameSpel", "%" + page.getConditions().get("compName").trim().toUpperCase() + "%");
				}
			}
			
			return dao.getSqlPageMapList(sql.toString(), params, page);
		} else {
			return null;
		}
	}
	
	@SuppressWarnings("unchecked")
	public Map<String, Object> getPorjInfoByGoodsCode(String goodsCode){
		StringBuffer sql = new StringBuffer()
		.append("select * from bid_project t")
		.append(" where t.proj_id =")
		.append(" (select DISTINCT(a.proj_id) from base_goods_item a where a.goods_code=:goodsCode)");
		Map<String, Object> params = new HashMap<String, Object>();
		params.put("goodsCode", goodsCode);
		List<Map<String, Object>> list = (List<Map<String, Object>>) dao.getSqlMapList(sql.toString(), params);
		if (null != list && list.size() > 0){
			return list.get(0);
		}
		return null;
	}
	@SuppressWarnings("unchecked")
	public void setBaseDelRelation(String goodsCode,List<BaseDelrelationForm> list,String userId,String gzfjydel){
		if (null != list && list.size() > 0){
			List<String> pl = new ArrayList<String>();
			Map<String, Object> params = new HashMap<String, Object>();
			for (int i = 0; i < list.size(); i++){
				BaseDelrelationForm form = list.get(i);
				StringBuffer sql = new StringBuffer();
				
				if("1".equals(gzfjydel)) {//非基药配送标识
					params = new HashMap<String, Object>();
					sql.append("SELECT a.del_comp_code,a.del_comp_name,a.del_comp_name_spel,a.del_comp_name_short,a.del_comp_status,a.area_code,a.area_name,a.ar_shortname,a.ar_fullname from base_del_area_rel a where a.del_comp_code = :compCode");
					params.put("compCode", form.getCompCode());
					List<Map<String, Object>> delList = (List<Map<String, Object>>) dao.getSqlMapList(sql.toString(), params);
					for(int j =0;j<delList.size();j++) {
						sql = new StringBuffer();
						Map<String, Object> map = delList.get(j);
						params.remove("compCode");
						String uuid = UUID.randomUUID().toString();
						pl.add(uuid);
						Map<String, Object> goods = this.getBaseGoodsByCode(goodsCode);
						
						sql.append("insert into base_delrelationship(del_rel_id,goods_code,prod_comp_code,prod_comp_name,prod_comp_name_spel,prod_comp_status,area_code,area_name,ar_shortname,ar_fullname,del_comp_code,del_comp_name,del_comp_name_spel,del_comp_name_short,del_comp_status,del_range,status,upd_user,upd_datetime) ")
						.append(" values(:uuid,:goodsCode,:prodCompCode,:prodCompName,:prodNameSpel,:prodCompStatus,")
						.append(" :areaCode,:areaName,:arS,:arF,:delCode,:delName,:delSpel,:delNameS,:delStatus,")
						.append("0,1,:updUser,:updDatetime)");
						
						params.put("uuid", uuid);
						params.put("goodsCode", goodsCode);
						
						params.put("prodCompCode", goods.get("prod_compcode"));
						params.put("prodCompName", goods.get("prod_compname"));
						params.put("prodNameSpel", goods.get("prod_compname_spel"));
						params.put("prodCompStatus", goods.get("prod_compstatus"));
						
						params.put("areaCode", map.get("area_code"));
						params.put("areaName", map.get("area_name"));
						params.put("arS", map.get("ar_shortname"));
						params.put("arF", map.get("ar_fullname"));
						params.put("delCode", map.get("del_comp_code"));
						params.put("delName", map.get("del_comp_name"));
						params.put("delSpel", map.get("del_comp_name_spel"));
						params.put("delNameS", map.get("del_comp_name_short"));
						params.put("delStatus", map.get("del_comp_status"));
						
						params.put("updUser", userId);
						params.put("updDatetime", new Date());
						dao.executeSql(sql.toString(), params);
					}
				}else {
					String uuid = UUID.randomUUID().toString();
					pl.add(uuid);
					Map<String, Object> goods = this.getBaseGoodsByCode(goodsCode);
					
					sql.append("insert into base_delrelationship")
					.append(" select :uuid,:goodsCode,:prodCompCode,:prodCompName,:prodNameSpel,:prodCompStatus,")
					.append(" null,null,null,null,a.comp_code,a.comp_name,a.comp_spel_code,a.comp_short_name,a.status,")
					.append("null,null,null,null,0,1,:updUser,:updDatetime from std_compinfo a")
					.append(" where a.comp_code = :compCode");
					
					params.put("uuid", uuid);
					params.put("goodsCode", goodsCode);
					
					params.put("prodCompCode", goods.get("prod_compcode"));
					params.put("prodCompName", goods.get("prod_compname"));
					params.put("prodNameSpel", goods.get("prod_compname_spel"));
					params.put("prodCompStatus", goods.get("prod_compstatus"));
					
					params.put("updUser", userId);
					params.put("updDatetime", new Date());
					params.put("compCode", form.getCompCode());
					dao.executeSql(sql.toString(), params);
				}
			}
			StringBuffer logsql = new StringBuffer()
			.append("insert into base_delrelationship_log")
			.append(" select UUID(),a.* from base_delrelationship a ")
			.append(" where a.del_rel_id in (:id)");
			params.remove("uuid");
			params.remove("goodsCode");
			params.remove("updUser");
			params.remove("updDatetime");
			params.remove("compCode");
			params.remove("prodCompCode");
			params.remove("prodCompName");
			params.remove("prodNameSpel");
			params.remove("prodCompStatus");
			if("1".equals(gzfjydel)) {//非基药配送标识
				params.remove("areaCode");
				params.remove("areaName");
				params.remove("arS");
				params.remove("arF");
				params.remove("delCode");
				params.remove("delName");
				params.remove("delSpel");
				params.remove("delNameS");
				params.remove("delStatus");
			}
			params.put("id", pl);
			dao.executeSql(logsql.toString(), params);
		}
	}
	
	
	/**
	 * @category 保存不竞不限药品信息
	 * @author 韩守松
	 * @date   2016年1月13日
	 * @param  @param goods
	 */
	public void save(BaseGoods goods,SysUser user){
		Map<String,Object> prodCompMap = stdCompinfoDao.getCompinfoByCode(goods.getProdCode());
		String prodcompName = "";
		String prodcompSpel = "";
		Integer Prodstatus = 0 ;
		String prodCompShortName="";
		if(prodCompMap != null){
			prodcompName = prodCompMap.get("comp_name").toString();
			prodcompSpel = prodCompMap.get("comp_spel_code").toString();
			Prodstatus = Integer.valueOf(prodCompMap.get("status")==null?"0":prodCompMap.get("status").toString());
			prodCompShortName = prodCompMap.get("comp_short_name")==null?"":prodCompMap.get("comp_short_name").toString();
			goods.setCompName(prodcompName);
			goods.setCompNamespel(prodcompSpel);
			goods.setProdStatus(Prodstatus);
			goods.setCompShortname(prodCompShortName);
		}
		
		//药品信息入药品表
		StdProdDrugInfoForm form = new StdProdDrugInfoForm();
		form.setDrugCode(goods.getDrugCode());
		form.setQualityspecName(goods.getDrugName());
		form.setQualityspecId(goods.getQualityspecId());
		form.setFormName(goods.getFormName());
		form.setSpecName(goods.getSpecName());
		form.setProdCompCode(goods.getProdCode());
		form.setProdCompName(goods.getCompName());
		form.setProdCompspel(goods.getCompNamespel());
		form.setProdCompStatus(goods.getProdStatus());
		form.setApprovalCode(goods.getAppoveCode());
		form.setProdCompId(goods.getProdPompId());
		form.setBusName("无");
		form.setFactor(goods.getPriNum());
		form.setDrugAttr(0);
		form.setUnit(goods.getUnitId());
		form.setUnitCode(goods.getUnitCode());
		form.setUnitName(goods.getUnit());
		form.setUnitStatus(Status.enabled.getKey());
		
		String drugCode2 = stdDrugInfoDao.checkDrug(form);//获取药品表中是否已经有该药品编码
		if (StringUtils.isNotBlank(drugCode2)) {//判断药品编号是否为空
			goods.setDrugCode(drugCode2);
		}else {
			stdDrugInfoDao.saveNoJNoXDrugData(form,user);//插入药品表
		}
		
		
		
		//药品信息入商品表
		String goodsCode = commonService.getSequence("goodsCode").toString();
		goods.setGoodsCode(goodsCode);
		saveBaseGoods(goods);
		
		
		//药品信息入此用户采购目录表
		List<GoodsSelectForm> list = new ArrayList<GoodsSelectForm>(); 
		GoodsSelectForm goodForm = new GoodsSelectForm();
		goodForm.setDelCompCode(goods.getDelCode());
		goodForm.setHospCode(goods.getHospCode());
		goodForm.setGoodsCode(goodsCode);
		goodForm.setProPrice(goods.getProPriceLimit().toString());
		goodForm.setPriNum(goods.getPriNum());
		goodForm.setUpdUser(user.getId());
		list.add(goodForm);
		purHospCatalogDao.batchSaveGoodsSelect(list);
	}
	
	
	
	/**
	 * @category 药品信息存入商品表
	 * @author 韩守松
	 * @date   2016年1月13日
	 * @param  @param goods
	 */
	public void saveBaseGoods(BaseGoods goods){
		Map<String,Object> projMap = getProjData();
		if (checkSameDrug(goods)) {
			throw new MSException("商品已存在！");
		}
		if(!checkBidProj(String.valueOf(projMap.get("proj_id")),goods.getDrugCode(),goods.getProdCode())) {//判断项目药品关联表是否存在
			//项目药品关系表数据插入
			bidProjectDao.saveProjDrug(projMap,goods);
		}
		
		StringBuffer hql =  new StringBuffer();
		hql.append("insert into base_goods_item( ");
		hql.append(" goods_code,area_id,area_name,ar_shortname,ar_fullname,proj_id,proj_name,proj_starttime,proj_endtime,proj_status, ");
		hql.append(" qualityspec_id,drug_code,qualityspec_name,qualityspec_name_spel,qualityspec_name_eng,form_name,spec_name, ");
		hql.append(" qualityspec_status,bus_name,factor,unit_name,material_name,prod_compcode,prod_compname,prod_compname_short, ");
		hql.append(" prod_compname_spel,prod_compstatus,cat_comp_code,cat_comp_name,cat_comp_name_short,cat_comp_name_spel,");
		hql.append(" cat_comp_status,use_range,drug_attr,goods_source,pattern_type,is_healthcare,is_agriculture,is_lowprice,");
		hql.append(" is_smallprice,is_smallsource,is_longtime_plan,drug_status,drug_cat,drug_cat_name,pro_price_limit,");
		hql.append(" retail_price_limit,hang_price,ref_price,healthcare_price,pic_url,pic_short_url,status,upd_user,upd_datetime,");
		hql.append(" middle_stand_rate,big_middle_rate,bid_no,price_permit_number,reg_end_date,gmp_end_date ");
		hql.append(" ) ");
		hql.append(" values( ");
		hql.append(" :goods_code,:area_id,:area_name,:ar_shortname,:ar_fullname,:proj_id,:proj_name,:proj_starttime,:proj_endtime,:proj_status, ");
		hql.append(" :qualityspec_id,:drug_code,:qualityspec_name,:qualityspec_name_spel,:qualityspec_name_eng,:form_name,:spec_name, ");
		hql.append(" :qualityspec_status,:bus_name,:factor,:unit_name,:material_name,:prod_compcode,:prod_compname,:prod_compname_short, ");
		hql.append(" :prod_compname_spel,:prod_compstatus,:cat_comp_code,:cat_comp_name,:cat_comp_name_short,:cat_comp_name_spel,");
		hql.append(" :cat_comp_status,:use_range,:drug_attr,:goods_source,:pattern_type,:is_healthcare,:is_agriculture,:is_lowprice,");
		hql.append(" :is_smallprice,:is_smallsource,:is_longtime_plan,:drug_status,:drug_cat,:drug_cat_name,:pro_price_limit,");
		hql.append(" :retail_price_limit,:hang_price,:ref_price,:healthcare_price,:pic_url,:pic_short_url,:status,:upd_user,:upd_datetime,");
		hql.append(" :middle_stand_rate,:big_middle_rate,:bid_no,:price_permit_number,:reg_end_date,:gmp_end_date ");
		hql.append(" ) ");
		SQLQuery query = getSession().createSQLQuery(hql.toString());
		query.setParameter("goods_code", goods.getGoodsCode());
		query.setParameter("area_id", null);
		query.setParameter("area_name",null );
		query.setParameter("ar_shortname",null );
		query.setParameter("ar_fullname", null);
		query.setParameter("proj_id",projMap.get("proj_id") );
		query.setParameter("proj_name",projMap.get("name") );
		query.setParameter("proj_starttime",projMap.get("starttime") );
		query.setParameter("proj_endtime",projMap.get("endtime") );
		query.setParameter("proj_status", projMap.get("status"));
		query.setParameter("qualityspec_id", goods.getQualityspecId());
		query.setParameter("drug_code",goods.getDrugCode() );
		query.setParameter("qualityspec_name",goods.getDrugName() );
		query.setParameter("qualityspec_name_spel", PinYin.getPinYinHeadChar(goods.getDrugName()));
		query.setParameter("qualityspec_name_eng", null);
		query.setParameter("form_name",goods.getFormName() );
		query.setParameter("spec_name", goods.getSpecName());
		query.setParameter("qualityspec_status", DrugStatus.enabled.getKey());
		query.setParameter("bus_name",goods.getDrugName() );
		query.setParameter("factor",goods.getPriNum());
		query.setParameter("unit_name", goods.getUnit());
		query.setParameter("material_name", null);
		query.setParameter("prod_compcode",goods.getProdCode() );
		query.setParameter("prod_compname", goods.getCompName());
		query.setParameter("prod_compname_short",goods.getCompShortname());
		query.setParameter("prod_compname_spel",goods.getCompNamespel());
		query.setParameter("prod_compstatus",goods.getProdStatus());
		query.setParameter("cat_comp_code", goods.getProdCode() );
		query.setParameter("cat_comp_name", goods.getCompName());
		query.setParameter("cat_comp_name_short",goods.getCompShortname());
		query.setParameter("cat_comp_name_spel", goods.getCompNamespel());
		query.setParameter("cat_comp_status",goods.getProdStatus() );
		query.setParameter("use_range", 0);
		query.setParameter("drug_attr",2 );
		query.setParameter("goods_source",GoodsSource.bjbx.getKey() );
		query.setParameter("pattern_type",9 );
		query.setParameter("is_healthcare", 0);
		query.setParameter("is_agriculture", 0);
		query.setParameter("is_lowprice", 0);
		query.setParameter("is_smallprice",0 );
		query.setParameter("is_smallsource",0 );
		query.setParameter("is_longtime_plan",1 );
		query.setParameter("drug_status", DrugStatus.enabled.getKey());
		query.setParameter("drug_cat", goods.getDrugCat());
		query.setParameter("drug_cat_name",goods.getDrugCatName());
		query.setParameter("pro_price_limit",goods.getProPriceLimit() );
		query.setParameter("retail_price_limit", goods.getProPriceLimit());
		query.setParameter("hang_price", null);
		query.setParameter("ref_price", null);
		query.setParameter("healthcare_price",null );
		query.setParameter("pic_url",null );
		query.setParameter("pic_short_url",null );
		query.setParameter("status",DrugStatus.enabled.getKey() );
		query.setParameter("upd_user", 1);
		query.setParameter("upd_datetime",new Date());
		query.setParameter("middle_stand_rate",null );
		query.setParameter("big_middle_rate", null);
		query.setParameter("bid_no",null );
		query.setParameter("price_permit_number",null );
		query.setParameter("reg_end_date", null);
		query.setParameter("gmp_end_date", null);
		query.executeUpdate();
		getSession().flush();
		getSession().clear();
	}
	
/*	*//**
	 * @category 商品日志写入
	 * @author 韩守松
	 * @date   2016年1月14日
	 * @param  @param id
	 *//*
	private void writeLog(String id) {
		String logId = UUID.randomUUID().toString();
		String sql = " INSERT INTO base_goods_item_log SELECT :logId, a.*,null FROM base_goods_item a WHERE a.goods_code = :id ";
		SQLQuery query = getSession().createSQLQuery(sql);
		query.setString("logId", logId);
		query.setString("id", id);
		query.executeUpdate();
		getSession().flush();
	}*/
	
	
	
	/**
	 * @category 校验商品是否存在
	 * @author 韩守松
	 * @date   2016年1月14日
	 * @param  @param form
	 * @param  @param exceptIds
	 * @param  @return
	 */
	public boolean checkSameDrug(BaseGoods goods) {
		StringBuilder sb = new StringBuilder();
		sb.append(" SELECT COUNT(*) FROM base_goods_item b WHERE b.qualityspec_name = :qualityspecName AND b.form_name = :formName AND b.spec_name = :specName AND b.unit_name = :unitName  AND b.prod_compcode = :prodCompCode ");
		SQLQuery query = getSession().createSQLQuery(sb.toString());
		query.setString("qualityspecName", goods.getDrugName());
		query.setString("formName", goods.getFormName());
		query.setString("specName", goods.getSpecName());
		query.setString("unitName", goods.getUnit());
		query.setString("prodCompCode", goods.getProdCode());
		BigInteger count = (BigInteger) query.uniqueResult();
		return count.longValue() > 0;
	}
	
	/**
	 * @category 判断项目药品关联表中是否存在
	 * @author lil
	 * @date   2016年2月3日
	 * @param  @param projId
	 * @param  @param drugCode
	 * @param  @param compCode
	 * @param  @return
	 */
	public boolean checkBidProj(String projId, String drugCode, String compCode) {
		StringBuilder sb = new StringBuilder();
		sb.append(" select count(*) from bid_projdrugs a where a.proj_id =:projId and drug_code=:drugCode and comp_code = :compCode ");
		SQLQuery query = getSession().createSQLQuery(sb.toString());
		query.setString("projId", projId);
		query.setString("drugCode", drugCode);
		query.setString("compCode", compCode);
		BigInteger count = (BigInteger) query.uniqueResult();
		return count.longValue() > 0;
	}

	/**
	 * @category 获取不竞不限商品数据信息
	 * @author 韩守松
	 * @date   2016年1月14日
	 * @param  @param page
	 * @param  @param data
	 * @param  @return
	 */
	public Pagination getNoJNoXGoodsData(Pagination page,DrugNoJNoX data){
		StringBuffer hql = new StringBuffer();

		hql.append(" SELECT  ");
		hql.append(" p.hosp_pro_cat_id hospProCatId,p.goods_code goodsCode,b.prod_compcode prodCompcode, b.drug_code drugCode, b.qualityspec_name qualityspecName,b.form_name formName,b.spec_name specName,b.factor,b.unit_name unitName, b.prod_compname compName,s.comp_code delCompCode,s.comp_name delCompName, b.status goodsStatus ");
		hql.append(" from  pur_hospcatalog  p ");
		hql.append(" join base_goods_item b on p.goods_code = b.goods_code and goods_source = :goodsSource ");
		hql.append(" join std_compinfo s on p.del_comp_code = s.comp_code ");
		hql.append(" where p.hosp_code = :hospCode ");
		Map<String, Object> params = new HashMap<String, Object>();
		params.put("goodsSource", GoodsSource.bjbx.getKey());
		params.put("hospCode", data.getHospCode());
		// 药品编码
		String drugCode = data.getDrugCode();
		if (StringUtils.isNotBlank(drugCode)) {
			hql.append(" AND b.drug_code = :drugCode ");
			params.put("drugCode",drugCode);
		}

		// 通用名
		String qualityspecName = data.getDrugName();
		if (StringUtils.isNotBlank(qualityspecName)) {
			hql.append(" AND (b.qualityspec_name LIKE :qualityspecName OR b.qualityspec_name_spel LIKE :qualityspecNameSpel) ");
			params.put("qualityspecName", "%" +qualityspecName.trim() +"%");
			params.put("qualityspecNameSpel", "%" + qualityspecName.trim().toUpperCase() +"%");
		}

		// 生产企业code
		String prodCompCode = data.getProdCode();
		if (StringUtils.isNotBlank(prodCompCode)) {
			hql.append(" AND b.prod_compcode = :prodCompCode  ");
			params.put("prodCompCode",prodCompCode);
		}
		
	
		// 生产企业名称
		String prodCompName = data.getProdName();
		if (StringUtils.isNotBlank(prodCompName)) {
			hql.append(" AND (b.prod_compname LIKE :prodCompName  OR b.prod_compname_spel LIKE :prodCompNameSpel) ");
			params.put("prodCompName", "%" +prodCompName.trim() +"%");
			params.put("prodCompNameSpel", "%" + prodCompName.trim().toUpperCase() +"%");
		}
		hql.append("  ORDER BY p.upd_datetime DESC  ");
		return dao.getSqlPageMapList(hql.toString(), params, page);
	}
	
	@SuppressWarnings("unchecked")
	public Map<String,Object> getProjData(){
		StringBuffer hql = new StringBuffer();
		hql.append("select * from bid_project where proj_code = :projCode ");
		SQLQuery query = getSession().createSQLQuery(hql.toString());
		query.setParameter("projCode", projareaProp.getProperty("projCode"));
		query.setResultTransformer(Transformers.ALIAS_TO_ENTITY_MAP);
		List<Map<String,Object>> list = query.list();
		return list.get(0);
	}
}
